Part of the free Module 5: Excel Formulas and Functions · Function 21 of 105 · Full Excel course
The Excel DAY function returns the day of the month from a date as a number from 1 to 31. It reads the date’s serial number, so it works on any real date or on text that Excel can recognise as a date. DAY is used to group transactions by day, test for month starts and ends, and rebuild dates together with YEAR and MONTH.
Syntax
=DAY(serial_number)Arguments
| Argument | Required / Optional | Meaning |
|---|---|---|
| serial_number | Required | The date to read: a cell containing a date, a function such as TODAY(), a DATE result or recognisable date text such as “2026-09-04”. |
How DAY works
Excel stores dates as whole numbers counting from 1 January 1900, with any time as a decimal fraction. DAY converts that number back into a calendar date and returns the day component only, ignoring the time. =DAY("2026-09-04 18:30") returns 4. Negative numbers and text that is not a date return #VALUE!; DAY(0) returns 0 because Excel treats serial 0 as the fictional 0 January 1900. The result is a plain number, so keep the cell in General format.
Step-by-step example: flag payments due at month end
- Enter invoice dates in column A, starting at A2.
- In B2 enter
=DAY(A2)and fill down. Each row shows the day number, for example 4 for 4 September 2026. - In C2 enter
=DAY(A2)=DAY(EOMONTH(A2,0))and fill down. It returns TRUE when the invoice falls on the last day of its month. - Filter column C for TRUE to list the month-end invoices.
Practical use cases
1. Number of days in a month
=DAY(EOMONTH(A2,0))EOMONTH returns the last date of the month and DAY reads its day number: 28, 29, 30 or 31.
2. First day of the current month
=A2-DAY(A2)+13. Split a month into halves for reporting
=IF(DAY(A2)<=15,"First half","Second half")4. Move every date to a fixed billing day
=DATE(YEAR(A2),MONTH(A2),15)Rebuilds the date with the day replaced, a common pattern with YEAR and MONTH.
5. Total sales on a given day number across months
=SUMPRODUCT((DAY($A$2:$A$500)=1)*$B$2:$B$500)Common mistakes and tips
- #VALUE! error: the input is text Excel cannot read as a date, such as “04.09.2026” on an English system. Convert it with DATEVALUE or SUBSTITUTE first.
- Day name wanted: DAY returns a number. Use
=TEXT(A2,"dddd")for Friday or WEEKDAY for a weekday index. - Result formatted as a date: a cell in date format shows 4 as 04/01/1900. Set the format to General.
- Dates stored as text: a whole column of left-aligned dates needs DATEVALUE or Text to Columns before DAY will work reliably.
- Regional order: text such as “09/04/2026” is interpreted by system settings; real date cells avoid the ambiguity.
DAY compared with WEEKDAY, DAYS and TEXT
These functions are often confused because their names overlap. DAY gives the day of the month (1 to 31). WEEKDAY gives the day of the week as a number (1 to 7 by default, Sunday first). DAYS counts the number of days between two dates. TEXT with “ddd” or “dddd” gives the weekday name as text, and with “dd” gives the day of the month as two-digit text. Choose DAY when you need a numeric day-of-month for grouping or rebuilding dates, WEEKDAY for weekend logic, DAYS for durations and TEXT only for display labels, because its output cannot be used in arithmetic.
Related functions
- MONTH: returns the month number from a date.
- YEAR: returns the year from a date.
- EOMONTH: returns the last day of a month, used with DAY to count days.
- WEEKDAY: returns the day of the week.
- See all lessons in the Excel Formulas course.
Frequently asked questions
How do I get the number of days in a month with DAY?
Use =DAY(EOMONTH(A2,0)). EOMONTH returns the last date of the month and DAY reads its day number.
Can the DAY function return the day name?
No. DAY returns a number from 1 to 31. Use =TEXT(A2,”dddd”) to get the weekday name.
Why does DAY return #VALUE!?
The argument is text that Excel cannot interpret as a date in your regional settings, or it is not a date at all. Convert it with DATEVALUE or DATE first.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.