Excel DAY Function

DAY takes a date and returns the day of the month as a number between 1 and 31. It's a simple function with one argument, but it fails silently more often than you'd expect: feed it a text date in the wrong regional format, and it will return a day that looks plausible but is wrong.

Syntax

=DAY(serial_number)

Parameter	Required	Description
`serial_number`	Yes	The date you want the day extracted from. Can be a cell reference, a date typed directly (in quotes), or the result of another formula like `TODAY()`. Excel stores dates as sequential numbers, so DAY is really reading a number, not a calendar.

ℹ

Excel dates are stored as serial numbers starting at 1 for January 1, 1900. DAY works by reading that underlying number, not the formatted display.

Basic Example

You're auditing an invoice log and need to flag any invoice dated after the 25th, since those get pushed to next month's billing cycle.

=DAY(C2)

// C2  =  the invoice date, 3/14/2026

With 3/14/2026 in C2, this returns 14. Wrap it in a comparison, =DAY(C2)>25, and you've got a one-cell test for late-cycle invoices without touching MONTH or YEAR at all.

How DAY Works

DAY only returns the day of the month

DAY has nothing to do with the day of the week. If you want to know that March 14, 2026 falls on a Saturday, you need WEEKDAY or TEXT with a "dddd" format. DAY answers a narrower question: which day of the month is this, 1 through 31.

DAY accepts dates, serial numbers, and text dates, but not equally well

=DAY(45700) works, because 45700 is a valid Excel serial number. =DAY("6/12/2026") also works, because Excel can parse that text into a date on the fly. The risk is regional ambiguity: a date like "03/04/2026" reads as March 4 under a US locale and April 3 under a UK or European one. If your workbook or your data source uses a different regional format than your Excel install, DAY will return a number that looks fine and is wrong.

DAY ignores time values

A date-time value like 3/14/2026 6:45 PM still returns 14. The time component lives in the decimal portion of the serial number and DAY simply doesn't look at it. You don't need to strip the time out first.

DAY handles leap years automatically

=DAY("2/29/2024") returns 29 without any special handling, because Excel's date system already accounts for leap years. You don't need to add any special logic for February in a leap year versus a non-leap one.

Common Use Cases

Flagging month-end transactions

You're building a billing tracker and need to catch orders placed in the last three days of any month.

=DAY(order_date)>=DAY(EOMONTH(order_date,0))-2   // TRUE for the last 3 days of the month

Rebuilding a date after changing the day component

You need to move every invoice in a batch to the 1st of its month, without disturbing the month or year.

=DATE(YEAR(invoice_date), MONTH(invoice_date), 1)

// YEAR(invoice_date)   =  keeps the original year
// MONTH(invoice_date)  =  keeps the original month
// 1                    =  forces the day to the 1st

Building a semi-monthly payroll flag

Payroll runs on the 15th and the last day of the month. You want a formula that flags which pay period a transaction date falls into.

=IF(DAY(transaction_date)<=15, "First Half", "Second Half")

Sorting invoices by day-of-month regardless of year

You're comparing seasonal patterns across three years of sales data and want to group transactions by day of the month only.

=DAY(sale_date)   // returns 1-31, ignoring the year entirely

Handling Errors

DAY throws #VALUE! when serial_number isn't something Excel can interpret as a date. This is the only error DAY produces directly.

Common causes of #VALUE!:

The cell contains plain text that isn't formatted or parseable as a date ("March fourteen" instead of "3/14/2026")
The date falls before January 1, 1900, which Excel's date system doesn't support

A blank cell is a separate problem entirely. DAY doesn't throw #VALUE! for an empty reference, it returns 0, and that 0 passes silently into any downstream math or IF logic that expects a real date.

=IFERROR(DAY(C2), "Check date format")

This catches genuine parsing failures, like text Excel can't read as a date. It won't catch a blank C2, since DAY doesn't throw an error on a blank cell at all.

✓

If a blank cell is a real possibility in your data, test for it explicitly with =IF(C2="", "", DAY(C2)). IFERROR can't catch a 0 result, so relying on it alone will let blank cells slip through as valid data.

Notes & Gotchas

How do I use the DAY function in Excel?

Point DAY at any cell containing a date: =DAY(A2). It returns a plain number from 1 to 31 representing the day of the month, which you can then use in comparisons, IF statements, or as part of a rebuilt date with the DATE function.

How do I use the DAY formula in Google Sheets?

The syntax is identical: =DAY(A2). Google Sheets uses the same underlying serial date system as Excel, so a formula built in one will behave the same way in the other, with the same text-date parsing risks.

How do I add a day (or days) to a date in Excel?

You don't need DAY for this at all: dates are numbers, so =A2+1 adds one day and =A2+7 adds a week. DAY only extracts the existing day-of-month value; it doesn't add to a date or change one.

What is the difference between the DAY function and the DAYS function?

DAY takes one date and returns a single number, the day of the month. DAYS takes two dates and returns the count of days between them: =DAYS(end_date, start_date). They solve completely different problems despite the similar name, and mixing them up is a common source of confusion for anyone searching "day function excel."

Why does DAY return a date like 1/1/1900 instead of a number?

This isn't actually DAY returning the wrong thing, it's a formatting problem. If the cell containing the formula inherited a date format (from copying a date cell, for example), Excel displays the numeric result as a date instead of a plain number. Select the cell, go to Home > Number Format, and change it to General or Number to see the actual day value.

Does DAY work with negative or out-of-range serial numbers?

No. Any serial number that resolves to a date before January 1, 1900, returns #VALUE!, since Excel's date system has no representation for it. This rarely comes up in practice, but it's worth knowing if a formula elsewhere in your workbook is subtracting dates and accidentally producing a negative serial number that then feeds into DAY.

⚠

DAY doesn't validate regional date formats. "5/6/2026" parses as May 6 on a US-locale install and June 5 on many European ones. If your source data comes from a different region than your Excel settings, wrap suspect text dates in DATEVALUE first and check the result before trusting DAY's output.

Related Functions

Function	Use this when...
`MONTH`	You need the month number instead of the day of the month.
`YEAR`	You need to extract the year from a date.
`DATE`	You need to reassemble a date from separate year, month, and day values.
`DAYS`	You need the number of days between two dates, not a single day-of-month value.
`WEEKDAY`	You need to know which day of the week a date falls on, not which day of the month.
`EOMONTH`	You need the last day of a month, useful for month-end and days-in-month calculations.