The Excel DAY function pulls the day-of-the-month number (1–31) out of any date. Feed it an invoice date and it tells you the 15th; pair it with MONTH and YEAR to take any date apart, and with DATE to put it back together shifted. One thing it is not: the day of the week — that’s WEEKDAY’s job.
15 — a plain number you can compare, sum, or feed into other formulas.
Syntax
| Argument | Description | |
|---|---|---|
serial_number | Required | The date to extract from — a cell containing a date, a DATE formula, or a raw serial number. Text that merely looks like a date may work but is risky; convert it first. |
Why "serial_number"? Excel stores every date as a serial number — days counted since January 1, 1900 (June 15, 2026 is 46188; times are fractions of a day). DAY just reads the day component out of that count, which is why =DAY(46188) and =DAY("6/15/2026") both return 15.
Extract the day of the month
Point DAY at any real date and it returns the calendar day as a plain number:
| A | B | C | |
|---|---|---|---|
| 1 | Invoice date | =DAY(A) | Meaning |
| 2 | 06/15/2026 | 15 | 15th of the month |
| 3 | 01/01/2026 | 1 | first of the month |
| 4 | 02/28/2026 | 28 | last day of Feb 2026 |
| 5 | 12/31/2026 | 31 | year-end |
That number plugs straight into logic. A classic billing rule — orders placed after the 25th bill next month:
Or rebuild the same date pinned to the first of its month (handy for grouping by month):
Try it: interactive DAY demo
Pick any date and watch DAY — alongside its siblings MONTH and YEAR — take it apart.
Practical day-of-month logic
DAY shines in date rules that depend on where in the month something happened:
The middle formula is a favorite: EOMONTH finds the last day of the month, and DAY converts that date into the month’s length — 28, 29, 30, or 31 — with no lookup table.
Need the day with a leading zero, as text? Use TEXT: =TEXT(A2, "dd") returns "15" or "05". DAY always returns a plain number.
Errors & common pitfalls
#VALUE! — the argument isn’t a date Excel recognizes. Text like "June 15th" or "15.06.2026" (in a US locale) can’t be coerced into a serial number. Convert with DATEVALUE or rebuild with DATE first.
Pitfall: DAY is not the day of the week. =DAY("6/15/2026") returns 15, not "Monday". For weekday numbers use WEEKDAY; for weekday names use =TEXT(A2, "dddd").
Pitfall: the result shows as a date, not a number. If =DAY(A2) displays something like 01/15/1900, the cell inherited a date format. The value (15) is right — set the format back to General and the number appears.
Pitfall: text dates that secretly aren’t dates. A column of left-aligned "dates" imported from a CSV is text; DAY may parse some entries and choke on others depending on locale. Convert the whole column to real serial numbers before extracting parts.
Practice workbook
Frequently asked questions
What's the difference between DAY and WEEKDAY?
How do I find how many days are in a month?
=DAY(EOMONTH(A2, 0)). EOMONTH returns the last date of A2’s month, and DAY turns it into 28, 29, 30, or 31 — leap years handled automatically.Why does DAY return #VALUE! on my data?
Why does =DAY(A2) show 01/15/1900 instead of 15?
Can DAY read a date that includes a time?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class