To extract the day of the month from an Excel date, put the date in A2 and enter =DAY(A2) in another cell. If A2 contains July 19, 2026, the result is 19. The DAY function returns a number from 1 to 31; it does not return the weekday name. Before starting, make sure Excel recognizes your source value as a date rather than text.
What You’ll Need
- A workbook in Microsoft Excel for Microsoft 365 on Windows. The same DAY formula works in Excel for Mac and Excel for the web.
- A column or cell containing dates that Excel can read as date values.
- An empty cell where you can put the extracted day, plus an optional heading such as Day.
Step-by-Step
- Check what you want to extract. In everyday use, day can mean the day number within a month, such as 19 in July 19, or the day of the week, such as Sunday. The steps below extract the day number. If you need a weekday name or weekday number, see the separate guidance in the Tips section. Being clear about this distinction prevents a correct formula from producing an unexpected-looking answer.
- Locate the source date. Find the cell with the date you want to use. For the example in this guide, the date is in A2, and the result will go in B2. If your date is in a different cell, note its address; you will use that address in the formula. A date column with a heading in row 1 commonly has the first date in row 2, but your workbook may use a different arrangement.
- Confirm Excel recognizes the date. Click the source cell and look at the formula bar and the cell display. A date that Excel recognizes usually behaves like a number: changing its number format can show it in another date style or as a serial value. A value imported from a text file may look exactly like a date while remaining text. If you are uncertain, try changing the cell’s number format from Home > Number Format to Short Date. If the display does not change as expected, use the troubleshooting advice below before relying on the result.
- Choose the output cell. Click B2, or the empty cell beside the first source date. Add a column heading such as Day in B1 if you want the extracted values to be easy to identify. Keep the source dates in their original column; using a separate output column makes it easier to check the calculation and preserves the full date.
- Enter the formula. With the date in A2 and the result intended for B2, type
=DAY(A2)in B2 and press Enter. For example, if A2 contains July 19, 2026, this returns 19. Replace A2 with the address of your actual date cell if it is elsewhere. The function takes one date value as its argument and returns the day number of that date. - Check the displayed result. A valid result should be a whole number from 1 through 31. If the source date is the first day of a month, the result is 1; if it is the last day, it may be 28, 29, 30, or 31, depending on the month and year. The result is not automatically padded with a leading zero, so a day of 5 normally displays as 5 rather than 05.
- Copy the formula down for more dates. Select B2 and point to the small square at its lower-right corner, called the fill handle. Drag the handle down alongside the remaining source dates. Excel adjusts the cell reference for each row, so the next result uses the date on that row. You can also copy B2, select the destination cells below it, and paste. On Windows, select the range to fill and press Ctrl+D to fill down; on Mac, the equivalent shortcut is Command+D. In Excel for the web, dragging the fill handle or copying and pasting is a straightforward option.
- Review a few rows against the original dates. Compare the extracted numbers with the day portions of the dates, especially the first and last rows. Check at least one date near a month boundary, such as the 1st or 31st. If the values look correct, the formula can remain in place and update when the dates change. If you need a fixed snapshot instead, copy the result cells and use Home > Paste > Paste Values. This replaces the formulas with their current numbers, so make sure the results are right before doing so.
Common Mistakes
Confusing a day number with a weekday. The DAY function returns the calendar date number inside the month. A result of 19 says that the date is the 19th; it does not say whether that date is a Monday or Sunday. If your task involves grouping by weekday, scheduling workdays, or displaying a weekday name, choose a weekday method instead.
Using a date that is actually text. Formatting cannot turn every text string into a real date. Excel may not calculate from a value that only looks like a date, and imported data can contain spaces or a date order that does not match your regional settings. Check and convert the source data before filling the formula down a large list.
Overwriting the source dates. Typing the extraction formula into the date cell replaces the date. Put the formula in a separate column so you retain the original information and can compare the output with its source.
Expecting a leading zero or a full date. The result is a number, not a date formatted as a two-digit text string. A 5 is the same day number as 05 for calculations. Also, the formula does not preserve the month or year; it returns only the day component.
Forgetting references change when copying. When a formula is filled down, Excel normally changes A2 to A3, A4, and so on. That is wanted when each row has its own date. If every output should refer to one fixed source cell, use an absolute reference by adding dollar signs to the cell reference before filling, or copy the formula without changing the reference.
Tips
Display the day with two digits when presentation requires it. The DAY result is numeric, which is useful for sorting and calculations. If a report specifically needs a visual value such as 05, format the output cells with a custom number format of 00. Select the output cells, open the Format Cells dialog, choose Custom, and enter 00 in the Type box. This changes how the number looks, not the underlying numeric value. Keep the result numeric if you plan to calculate with it.
Use a weekday method when you mean Monday or Tuesday. With the date in A2, the formula =TEXT(A2,"dddd") displays the full weekday name, such as Sunday for July 19, 2026. To show an abbreviated name, use the format code ddd instead. These formulas return text, which can be useful for labels, but text results are not the same as day numbers. Names shown can also depend on Excel’s language and regional settings.
Return a weekday number for analysis. With the date in A2, =WEEKDAY(A2,2) returns 1 for Monday through 7 for Sunday. The second argument sets Monday as the first day of the week. This differs from the day-of-month number, and the numbering convention matters when comparing results with a calendar or business rule. Keep the selected convention consistent throughout your workbook.
Keep formulas next to the dates they describe. A clear heading and a neighboring output column make it easier for another person to understand the workbook. If you convert formulas to values for a report, retain a copy of the original formula-based sheet or workbook so changes to the source dates can still be reflected later.
Use the same steps across Excel editions. The DAY function is available in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. The formula syntax is the same. The interface for filling or formatting cells can differ slightly, but typing the function and pressing Enter works the same way.
Troubleshooting
The cell shows a formula instead of a result. The output cell may be formatted as Text, or the formula may have been entered with a leading apostrophe. Change the output cell format to General, click the cell, press F2, and then press Enter on Windows. On Mac, use the equivalent edit action from the keyboard or formula bar and confirm the entry. Also check that the formula begins with an equals sign and has no apostrophe before it.
The result is an error or Excel rejects the entry. Confirm that the referenced cell is the one containing the date and that the formula’s parentheses are balanced. DAY needs a single date value, so it does not need a separator between arguments. If the source is text, convert it to a genuine Excel date. For a consistent imported format, select the affected column and use Data > Text to Columns, then follow the prompts and choose the date order that matches the data. Check a sample first, because choosing the wrong order can swap month and day.
The answer is a number that seems unrelated to the date. Excel stores real dates as serial numbers, and a cell’s format controls how that number is displayed. DAY extracts the day from the date value itself. Verify the source cell is the intended date and that it has not been imported with a different interpretation, such as month/day instead of day/month. A displayed date can appear plausible even when Excel interpreted its parts differently.
The formula works in the first row but not farther down. Check that each later row actually contains a valid date and that no blank, text, or error value interrupts the list. If you copied rather than filled, confirm the references point to the matching row. If you deliberately need a fixed source reference, make it absolute before filling. Also check whether the destination cells already contain data that prevented the fill from reaching them.
The displayed result has a date format. The calculation may be correct while the output cell’s formatting makes its number look like a date. Select the result cells and choose General or Number from Home > Number Format. The underlying extracted value remains a day number; changing the display format helps show it plainly.
Frequently Asked Questions
Does DAY return the day of the month or the weekday?
It returns the day of the month as a number from 1 to 31. Use a weekday function or a date display format if you need a weekday name or weekday number.
Can I extract the day from a date stored as text?
Not reliably until Excel recognizes the value as a date. Convert the text using a method that matches its actual date order, then check the result on several rows before filling the calculation down.
How do I extract the day for an entire column?
Put the formula beside the first date, then fill it down using the fill handle, copy and paste, or the fill-down shortcut. If the data is an Excel Table, entering the formula in the corresponding table column generally fills the calculated column automatically.
Can I show 05 instead of 5?
Yes. Keep the result numeric and apply the custom number format 00 to the output cells. This adds a display-leading zero without changing the value used in calculations.
Will the result update if I change the date?
Yes, as long as the output is still a formula and the new source value is a valid date. If you used Paste Values, the result is fixed and will not recalculate when the original date changes.
What if I need the month or year too?
DAY extracts only the day component. Excel also has separate date functions for extracting the month and year. Use separate output columns if you need all three parts, and keep the original full date when later calculations may depend on it.
Related
- How to Duplicate an Excel File Shortcut on Windows
- How to Duplicate a Page in Excel and Keep Its Layout
- Duplicate a Sheet in Excel: Keyboard Shortcut and Steps
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel CLEAN Function: Remove Nonprinting CharactersTo remove nonprinting characters from text in Excel, enter =CLEAN(A2) in a helper cell when the source text…
- Formula for Over or Under Budget in Excel: ExamplesTo calculate whether spending is over or under budget in Excel, subtract the budget from the actual amount:…
- Excel Formula to Password Protect a Sheet? What WorksNo Excel formula can password-protect a worksheet: formulas calculate values, but they cannot set sheet permissions or require…
- Excel Formula for Removing Spaces: TRIM and MoreIn Excel, use =TRIM(A2) to remove leading and trailing spaces and reduce repeated spaces between words to one.…
- Excel Sort and Filter by Multiple CriteriaIn Excel for Microsoft 365 on Windows, sort by several criteria with Data > Sort and add a…
- How to Filter Dates in Excel by Month, Year, or RangeTo filter dates in Excel, select a cell in your data, choose Data > Filter, open the arrow…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.