The DAY function returns the day of the month from a date, as a number from 1 to 31. It takes a single date (or a cell containing a date) and gives you just the day part, ignoring the month and year.
=DAY(serial_number)What does DAY do?
The DAY function is a simple but powerful tool in Excel's Date & Time category. You give it a date, and it tells you the day number—the 1 to 31 part of that date. For example, =DAY("2025-03-14") returns 14. It doesn't care about the month or year; it only extracts the day.
Why does that matter? Because many real-world tasks depend on the day of the month: grouping sales by day, checking if a payment is due, filtering birthdays, or building reports that show daily trends. Instead of manually reading dates, you let DAY do the work consistently and accurately.
Imagine you have a list of invoice dates and you need to know which ones fall after the 15th of the month. With DAY, you can write a formula like =IF(DAY(A2)>15,"Late","On time") and apply it to hundreds of rows in seconds. That's the kind of everyday efficiency DAY brings to office work and study projects.
When to use DAY
- Extracting the day number from a full date for reporting or sorting.
- Flagging transactions that occur after a certain day of the month.
- Calculating due dates or payment schedules based on the day.
- Grouping data by day of the month in pivot tables or summaries.
- Checking if a date falls on a specific day (e.g., the 1st or 15th).
- Building dynamic labels like "Day 5 of the month" in dashboards.
DAY syntax
=DAY(serial_number)| Argument | Required? | What to enter |
|---|---|---|
serial_number | Required | The date you want the day from. It can be a date value, a cell reference containing a date, or a number that Excel recognises as a date. For example, =DAY("2025-12-25") returns 25. |
What it returns: A whole number from 1 to 31 representing the day of the month of the given date.
DAY examples
1 Basic: Get the day from a date
This example shows the simplest use of DAY. The goal is to extract the day number from a single date in cell A2. The formula =DAY(A2) looks at the date 2025-03-14 and returns just the day part, which is 14. It ignores the month (March) and the year (2025). The result is a plain number you can use in further calculations.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Description | |||
| 2 | 2025-03-14 | Project deadline | 14 | ||
| 3 |
2 Using DAY with a text date
DAY can also work with dates entered as text, as long as Excel can interpret them. Here, cell A2 contains the text "2025-07-04". The formula =DAY(A2) converts the text to a date internally and returns 4. This is handy when you import data from other systems where dates come as text strings. The result is the number 4.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Holiday | Country | |||
| 2 | 2025-07-04 | USA | 4 | ||
| 3 |
3 Flagging late payments by day of month
Suppose payments are due by the 15th of each month. You have a list of payment dates and want to mark which ones are late. The formula =IF(DAY(A2)>15,"Late","On time") checks the day number in A2. If it's greater than 15, it returns "Late"; otherwise "On time". For 2025-04-18, DAY returns 18, so the result is "Late". This quickly highlights problem payments.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Payment Date | Customer | |||
| 2 | 2025-04-18 | Acme Ltd | Late | ||
| 3 | 2025-04-10 | Beta Corp | |||
| 4 | 2025-04-22 | Gamma Inc | |||
| 5 | 2025-04-05 | Delta Co | |||
| 6 |
4 Extracting day for monthly sales summary
You have daily sales figures and want to create a summary by day of the month. In this example, cell A2 contains the date 2025-05-12. The formula =DAY(A2) returns 12, which you can then use to group or label the sales data. This is often the first step before building a pivot table or chart that shows sales per day. The result is simply the number 12.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Sales | Region | ||
| 2 | 2025-05-12 | 1250 | North | 12 | |
| 3 |
Common DAY errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The serial_number argument is text that Excel cannot interpret as a date, such as "hello" or "31/31/2025". | Ensure the cell contains a valid date or a date string in a recognised format. Use DATEVALUE to convert text dates, or re-enter the date properly. |
| #NUM! | The serial_number is a negative number, which Excel cannot treat as a date. | Check that the date is positive. If you're calculating a date, make sure the result isn't before 1900-01-01. |
| Wrong day returned | The cell contains a date-time value and you expected only the date part, but DAY still returns the correct day. If the result seems off, the cell may be formatted as text or contain a different date than you think. | Verify the underlying value by selecting the cell and looking at the formula bar. Use ISTEXT to check if it's text, and convert if needed. |
| #NAME? | The function name is misspelled, e.g., =DAYS(A2) instead of =DAY(A2). | Type =DAY( and let Excel's autocomplete suggest the correct function. |
Tips for using DAY
- Use DAY together with MONTH and YEAR to break a date into its parts for custom formatting or calculations.
- If your dates are stored as text, convert them with DATEVALUE before using DAY, or use Text to Columns.
- Combine DAY with IF to create simple flags, like marking weekends or mid-month deadlines.
- Remember that DAY returns a number, not a date—so you can use it in arithmetic without worrying about date formatting.
- When building reports, use DAY to create a helper column for grouping by day of month in pivot tables.
Frequently asked questions
What does the DAY function do in Excel?
DAY returns the day of the month from a date, as a number from 1 to 31. For example, =DAY("2025-12-25") returns 25.
Can DAY handle dates entered as text?
Yes, if the text is in a format Excel recognises as a date, such as "2025-12-25" or "25-Dec-2025". Otherwise, you'll get a #VALUE! error.
How is DAY different from DAYS?
DAY extracts the day number from a single date. DAYS calculates the number of days between two dates. They serve different purposes.
Why does DAY return a number instead of a date?
DAY is designed to give you just the day component as a plain number, so you can use it in calculations, comparisons, or as a label.
Can I use DAY with a cell that contains a formula?
Yes, as long as the formula returns a valid date. DAY will evaluate the result and extract the day number.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. DAY reference