The Excel DAY function, explained interactively.
DAY extracts the day-of-month from a date, returning an integer between 1 and 31. It’s the third leg of the date-decomposition trio: YEAR, MONTH, and DAY. Where DATE assembles a date from these three integers, DAY pulls the day component back out. Combine it with EOMONTH for month-end logic.
How to use DAY
- Type
=DAY(and supply a date — a cell reference containing a date, aDATE()formula, or any formula that returns a real Excel date serial. - Close the parenthesis. DAY returns an integer from 1 to 31 — there are no extra arguments.
- The result is a plain integer, not a date. Format the output cell as General or Number, never as Date.
- For month-end work, combine DAY with EOMONTH:
=DAY(EOMONTH(A2, 0))returns the last day number of A2’s month, and=DAY(EOMONTH(A2,0))-DAY(A2)gives days remaining.
WEEKDAY(). Pair with EOMONTH() for month-end logic.DATE() formula, or any formula returning a serial. Only the date portion matters; time components are discarded.| A | B | |
|---|---|---|
| 1 | Date | Serial |
| 2 | 2026-04-19 | 46131 |
| 3 | ||
| 4 | DAY result | 19 |
=DAY(DATE(2026, 4, 19)) → 19. That’s April 19, 2026 (Sunday) — result range 1–31.DAY syntax and arguments
One required argument — the date to extract from. See Microsoft’s official DAY reference for the canonical specification.
DATE() result, or any formula returning a date serial number. Text strings that look like dates (e.g. "2026-04-19") return #VALUE!; wrap with DATEVALUE to coerce text first. DAY returns values 1–31 for any valid date.DAY examples
Four patterns covering the most common real-world DAY use cases.
Example 1: Extract day-of-month from dates
The direct use — pull the day integer from each date in a column. The result is always a number 1–31.
Useful when you need to filter or group by day number — e.g. flagging all invoices issued on the 1st or 15th of the month. February 29 (leap day) returns 29.
Example 2: Last day of month using EOMONTH
Find how many days a given month has — without a lookup table. Works correctly for February in leap years.
EOMONTH(A2, 0) returns the last day of A2’s month as a full date. DAY extracts the day number: 28, 29, 30, or 31. Change A4 to =DATE(2024, 2, 1) — February 2024 is a leap year, so the result jumps from 28 to 29.
Example 3: Check if a date is the last day of its month
Flag month-end dates precisely — handles all month lengths and leap years without hard-coding any day counts.
If DAY(A2) equals the last day of A2’s month, the date is a month-end. Useful for accounting close logic, billing checks, and reporting period boundaries. Works on February 28 in non-leap years and February 29 in leap years.
Example 4: Days remaining in the month
Compute how many days are left in the month — useful for countdown dashboards and deadline calculations.
Returns 0 on the last day of the month, the full month length minus 1 on the first day. Replace A2 with TODAY() for a live countdown: =DAY(EOMONTH(TODAY(),0))-DAY(TODAY()).
Common DAY errors and fixes
Three failure modes — each with what to check and how to recover.
DAY returns #VALUE!
Cause: the argument is text, not a real date. Text cells that look like dates — from CSV imports, copy-paste, or cells formatted as text — are not date serials.
Wrap with DATEVALUE(): =DAY(DATEVALUE(A2)). Or replace the text with a DATE() formula. Check alignment: real dates right-align, text dates left-align.
DAY returns a serial number instead of 1–31
Cause: the output cell is formatted as Date. DAY returns a plain integer like 19, but a date format turns 19 into 1900-01-19.
Format the result cell as General or Number. DAY’s output is an integer — never format it as a date.
DAY returns a day-of-month, not a day-of-week
Cause: confusion between the function name and what it returns. DAY always returns the day-of-month (1–31), not the weekday name or number.
For the day of the week (Monday, Tuesday, etc.) use WEEKDAY(A2) which returns 1–7, or TEXT(A2, "dddd") for the weekday name. DAY and WEEKDAY are separate functions.
YEAR, MONTH & DAY compared
The three date-extraction functions are a family — they share the same single-argument syntax and always return integers.
| Function | Returns | Range | Typical use |
|---|---|---|---|
| YEAR | Year integer | 1900–9999 | Fiscal year grouping, age approximation, period anchors |
| MONTH | Month integer | 1–12 | Monthly grouping, CHOOSE for month names, seasonal flags |
| DAY | Day-of-month integer | 1–31 | Month-end checks, days-remaining, payroll cut-off flags |
| DATE | Date serial | Any valid date | Reconstruct a date from YEAR/MONTH/DAY components |
Rule of thumb: YEAR, MONTH, and DAY decompose a date into integers; DATE assembles integers back into a date. The trio is most powerful together — for example, =DATE(YEAR(A2)+1, MONTH(A2), DAY(A2)) adds exactly one year to any date without EDATE.
DAY frequently asked questions
6.01What does the DAY function return in Excel?▸
DAY returns an integer between 1 and 31 representing the day-of-month component of the date passed to it. For example, DAY(DATE(2026, 4, 19)) returns 19. The result is always a whole number — it tells you which day of the month the date falls on, not the day of the week.
6.02How do I find the last day of the month using DAY?▸
Use =DAY(EOMONTH(A2, 0)). EOMONTH(A2, 0) returns the last day of the same month as A2 as a full date, then DAY extracts the day number. This returns 28, 29, 30, or 31 depending on the month and whether it’s a leap year.
6.03How do I check if a date is the last day of the month?▸
Compare DAY(A2) to DAY(EOMONTH(A2, 0)): =IF(DAY(A2)=DAY(EOMONTH(A2,0)),"Month end","Not end"). This works correctly for all month lengths including February in leap years — you don’t need to hard-code 28, 29, 30, or 31.
6.04Does DAY work on text-stored dates?▸
No — DAY requires a real Excel date serial number. Text like "2026-04-19" returns #VALUE!. Wrap with DATEVALUE to convert first: =DAY(DATEVALUE(A2)). Or replace text dates with =DATE(year, month, day) formulas. Real dates right-align in cells; text dates left-align.
6.05How do I calculate how many days are left in the current month?▸
Use =DAY(EOMONTH(A2, 0)) - DAY(A2). This subtracts today’s day-of-month from the last day of the month. If today is April 19 and the month ends on April 30, the result is 11. To use the actual current date, replace A2 with TODAY(): =DAY(EOMONTH(TODAY(), 0)) - DAY(TODAY()).
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.