Date & Time
DAY Function in Excel
Returns the day of a date as a number from 1 to 31.
Syntax
- =DAY(serial_number)
Arguments
- serial_number (required): The date to extract day from
Examples
- =DAY(TODAY()) - Current day of month - Result: 21
- =DAY(A1) - Day from date in cell - Result: Day number
- =DAY(EOMONTH(A1,0)) - Days in month - Result: 28, 29, 30, or 31
Excel DAY function documentation
- DAY returns the day of the month (1–31) from a date serial.
- Syntax: =DAY(serial_number). Works on dates, datetimes, and results from [DATE](/functions/date/).
- Use in billing cycles, due-date rules, and calendar dashboards.
- Related: [MONTH](/functions/month/), [YEAR](/functions/year/), [EOMONTH](/functions/eomonth/), [WEEKDAY](/functions/weekday/).
DAY syntax and datetime notes
- serial_number must be valid date — text dates may need DATEVALUE.
- DAY ignores time portion of datetime for day-of-month extraction.
- Combine DAY, MONTH, YEAR to rebuild dates or test anniversaries.
- #VALUE! if argument is not a date Excel recognizes.
Step-by-step: invoices due on the 15th
- Step 1 — Invoice dates in column A.
- Step 2 — Due this month on 15th: =DATE(YEAR(A2), MONTH(A2), 15) adjusting if invoice after 15.
- Step 3 — Flag: =IF(DAY(A2)>15, "Next month cycle", "Current cycle").
- Step 4 — Count due today: =SUMPRODUCT((DAY(A:A)=DAY(TODAY()))*1).
- Step 5 — Use [EOMONTH](/functions/eomonth/) for last-day-of-month rules instead of DAY alone.
DAY vs TEXT vs EOMONTH
- DAY — numeric day for logic.
- TEXT(A2,"d") — formatted string.
- EOMONTH — last day of month — better for month-end billing.
- Hub: [Date & Time functions](/categories/date-time/).
Worked examples to copy
- Day only: =DAY("2026-06-12").
- From TODAY: =DAY(TODAY()).
- First week: =IF(DAY(A2)<=7,"Week 1","Later").
- Birthday day: =DAY(dob) for anniversary reminders (pair MONTH).
People also ask
- DAY on text date? — May #VALUE! — use DATEVALUE first.
- DAY leap year Feb 29? — Returns 29 when date is Feb 29.
- DAY vs WEEKDAY? — DAY is date of month; WEEKDAY is Mon–Sun index.
- DAY in conditional format? — Rule =DAY(A2)=15 highlights 15th.
Common errors
- Returns 1-31
- Not day of week (use WEEKDAY)
Use cases
- Day extraction
- Due date calculations
- Calendar functions
Frequently asked questions
- How do I get day of week instead of day of month? Use WEEKDAY: =WEEKDAY(A1) returns 1-7 (Sunday-Saturday by default). For name: =TEXT(A1, "DDDD") returns "Wednesday".
- How do I find how many days are in a month? Use =DAY(EOMONTH(A1, 0)). EOMONTH gives last day of month, DAY extracts the day number (28, 29, 30, or 31).
- How do I check if it's the last day of the month? Use =A1=EOMONTH(A1, 0) which returns TRUE if A1 is the last day of its month.
- How do I calculate days until end of month? Use =EOMONTH(A1, 0)-A1. This gives days remaining in the month from date A1.
- How do I get the ordinal suffix (1st, 2nd, 3rd)? Use: =DAY(A1)&IF(OR(DAY(A1)={11,12,13}),"th",IF(MOD(DAY(A1),10)=1,"st",IF(MOD(DAY(A1),10)=2,"nd",IF(MOD(DAY(A1),10)=3,"rd","th"))))
Editorial review
- Reviewed by Excel.Directory Editorial Team. Updated May 2026.
When to use DAY
- Day extraction — common Date & Time scenario for DAY.
- Due date calculations — common Date & Time scenario for DAY.
- Calendar functions — common Date & Time scenario for DAY.
DAY in the Date & Time category
- Browse all Date & Time functions at /categories/date-time/ for related formulas.
- DAY syntax: =DAY(serial_number)
- serial_number (required): The date to extract day from
- Confirm DAY arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
Formula checklist before you copy down
- Confirm DAY arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
- If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
- Spot-check three known input rows manually against expected output.
Related Excel functions
- YEAR (/functions/year/): Returns the year of a date as a four-digit number.
- MONTH (/functions/month/): Returns the month of a date as a number from 1 to 12.
- DATE (/functions/date/): Creates a date from year, month, and day components.
- WEEKDAY (/functions/weekday/): Returns the day of the week for a date as a number.
Errors to watch for
- Returns 1-31 — review causes on linked error pages in the directory.
- Not day of week (use WEEKDAY) — review causes on linked error pages in the directory.
Copy-paste audit workflow
- Enter DAY on three test rows with known expected output documented on a QA tab.
- Fill down only after absolute references are locked on lookup tables and rate tables.
- Compare against manual calculation or a calculator for financial and statistical functions.
- Search this directory for comparison guides when choosing between similar functions in the same category.
DAY worked examples to copy
- =DAY(TODAY()) — Current day of month. Expected result: 21.
- =DAY(A1) — Day from date in cell. Expected result: Day number.
- =DAY(EOMONTH(A1,0)) — Days in month. Expected result: 28, 29, 30, or 31.
DAY reference summary for crawlers and offline review
- DAY belongs to the Date & Time category in Excel. Returns the day of a date as a number from 1 to 31.
- Full syntax: =DAY(serial_number). Open /functions/day/ for parameters, FAQs, and related pages.
- Common mistakes: Returns 1-31; Not day of week (use WEEKDAY)
- Pair this function with comparison guides when another Excel formula might fit the same task better.
- Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.