Excel Day Value: Serial Dates and the DAY Function

An Excel day value can mean either the serial number Excel uses to store a date or the day of the month returned by the DAY function. For example, March 15, 2024 is stored as serial number 45366 in Excel’s standard 1900 date system, while =DAY(A2) returns 15 if A2 contains that date.

Two meanings of Excel day value

The phrase is easy to misunderstand because Excel works with dates in two related ways. A date has an underlying numeric value, which makes it possible to sort dates and do arithmetic. Separately, a date can be broken into calendar parts: its year, month, and day of the month. The serial number and the day-of-month result are not interchangeable.

Date serial: A number representing a date in Excel’s date system. A date such as March 15, 2024 has serial number 45366 in the 1900 system.
Day of the month: An integer from 1 to 31. The DAY function extracts this part from a date; for March 15, its result is 15.
Day of the week: A weekday number or name, such as Monday. This is a different calculation, typically done with WEEKDAY or a custom date format.

If a cell shows a large number when you expected a date, the value may be correct but the cell is formatted as General or Number. If a formula returns 15 when you expected a serial number, it may be using DAY to extract the day of the month.

How Excel stores dates

In the standard 1900 date system used by most Windows workbooks, Excel represents dates as sequential numbers. The whole-number portion identifies the date, and dates later in the calendar have larger numbers. Excel can therefore calculate elapsed time by subtracting one date value from another. The display you see is controlled separately by the cell’s number format.

For example, the serial number 45366 represents March 15, 2024 in this system. If the cell is formatted as a date, Excel displays a date such as 3/15/2024 or 15-Mar-2024, depending on the selected format and regional settings. If it is formatted as General, it displays the underlying number instead. Changing the format does not change the stored date.

Excel’s 1900 system includes a historical compatibility quirk: it treats 1900 as if it were a leap year, even though it was not. This preserves compatibility with older spreadsheet software. As a result, serial number 60 represents the nonexistent date February 29, 1900; serial 61 represents March 1, 1900. This seldom affects modern dates, but it matters when working with very old dates or comparing serial values across different software.

What the DAY function returns

Use DAY when you want the calendar day number within a month, not the date’s serial value. The result is always an integer from 1 through 31, depending on the date. It does not return the day of the week, and it does not return how many days have elapsed since another date.

With the date March 15, 2024 stored in cell A2, enter =DAY(A2) in another cell. The result is 15. The input can be a cell containing a valid Excel date or a date value produced by another formula. If the input is text that Excel cannot recognize as a date, the formula can return an error rather than a day number.

For a date assembled directly from year, month, and day numbers, use the DATE function as the input. With no particular cell layout required, =DAY(DATE(2024,3,15)) returns 15. DATE constructs a valid date value, then DAY extracts its day component. This is often clearer and more reliable than typing a date as text inside a formula.

To extract other calendar parts, use YEAR for the year and MONTH for the month. With a valid date in A2, =MONTH(A2) returns 3 for March, and =YEAR(A2) returns 2024. Each formula returns a component, not a formatted version of the original date.

Show the serial number or change the date display

To inspect a date’s stored serial, select the date cell and change its number format to General. On Windows, use Home > Number Format and choose General; you can also press Ctrl+Shift+~. On Mac, the equivalent shortcut is Control+Shift+~; the number-format controls are on the Home tab, though their placement can vary by Excel version. The cell will display its numeric value, but it will still contain the same value.

To show a date again, select a date format from Home > Number Format, or choose More Number Formats to set a custom format. For instance, a custom format can display the same date as 15-Mar, March 15, or 2024-03-15. These formats alter appearance only; they do not turn the date into a different day or change its serial.

With the date March 15, 2024 in A2, enter =A2 in B2. B2 contains the same serial value as A2. If B2 is formatted as General, it displays 45366; if it is formatted as a date, it displays a date instead. This distinction is useful when checking whether a calculation is wrong or only the cell format is unexpected.

Use date values in calculations

Because dates are numbers, adding or subtracting a whole number moves a date by that many days. With March 15, 2024 in A2, enter =A2+7 in B2. The result is March 22, 2024, provided B2 is formatted as a date. If B2 uses General format, it displays 45373, which is the serial for March 22 in the 1900 system.

To calculate the number of days between two dates, use DAYS. With March 1, 2024 in A2 and March 15, 2024 in B2, enter =DAYS(B2,A2) in C2. The result is 14, the elapsed number of days from the start date to the end date. Reversing the arguments produces a negative result. If you need to count both the start and end dates as included calendar days, add one to the result; the inclusive count for this example is 15.

Be clear about what a calculation is intended to count. DAY returns a date’s position within its month, while subtracting dates or using DAYS measures the interval between dates. For example, DAY applied to March 15 returns 15, but the number of days from March 1 to March 15 is 14 when counting elapsed days. Confusing these meanings is a common source of off-by-one errors.

Date systems and workbook compatibility

Excel supports a 1900 date system and a 1904 date system. The 1900 system is the usual default for Windows workbooks. The 1904 system starts its date numbering from a different point, so the same calendar date has a serial number 1,462 days lower in that system. The visible calendar date can still be the same when the workbook is configured for the appropriate system.

This matters when copying or linking dates between workbooks created with different date systems. A raw serial number transferred without its date-system context may appear shifted by four years and one day. When dates look consistently wrong after moving data, check the workbook date-system setting as well as the cell format. On Windows, the setting is under File > Options > Advanced, in the section for calculating the workbook. The exact route for changing this setting on Mac can differ by Excel release.

Avoid changing a workbook’s date system casually. It can affect how existing date serials are interpreted, which may change displayed dates or calculations. If a workbook is shared with other people or connected to external data, check the impact on those dates before changing the setting.

Common problems and how to diagnose them

A date appears as a five-digit number: The cell is probably formatted as General or Number. Apply a date format if you want a calendar display; keep the number format if you are intentionally inspecting the serial.
DAY returns a number you did not expect: Confirm that you want the day of the month. DAY returns 1–31, not a serial or weekday. Check that the referenced cell contains the intended date.
A date is left-aligned or behaves like text: It may have been imported as text rather than stored as a date value. Text that looks like a date is not always usable in date arithmetic.
A date changes after copying between workbooks: The workbooks may use different date systems. Compare the date-system settings before trying to correct individual values.
A formula gives an unexpected date or time: Check whether the result cell is formatted as a date or time. Since a date-time value can include a fractional part, formatting can reveal or hide that part of the value.

For text dates, the interpretation can depend on regional settings. For example, 04/05/2024 can mean April 5 or May 4. With text in A2 that Excel can parse under the computer’s regional settings, =DATEVALUE(A2) converts it to a date serial. Format the result as General to inspect the serial or as a date to display the calendar date. For data with ambiguous formats, confirm the source’s date convention rather than assuming Excel can infer it correctly.

Choosing the right approach

Choose the method based on the result you need, not on the word day alone. Use a date format when you want a date to look different but remain a date. Use DAY when you need the day-of-month number for a comparison, report, or further calculation. Use DAYS or date subtraction when you need elapsed days between two dates. Use WEEKDAY when the question is about the weekday.

Before building a formula, check three things: whether the source cell is a real Excel date rather than text, whether you need a calendar component or a duration, and whether the result cell’s number format matches the intended output. These checks prevent many apparent errors without requiring a more complicated formula.

Frequently Asked Questions

What is the Excel day value for today? Excel stores today as a date serial that advances each day. To display today’s date, use a date format; to return its day-of-month number, use the DAY function with a current-date value.

Does DAY return the day of the week? No. DAY returns the day within the month, from 1 to 31. For a weekday number, use WEEKDAY; for a weekday name, apply an appropriate date format or use a formula that converts the weekday number to a name.

Why does Excel show a number instead of a date? The cell is likely formatted as General or Number. Apply a date number format to display a calendar date. The number is usually the underlying serial value, not evidence that the date has been lost.

Why is my date serial different in another workbook? The workbooks may use different date systems, or the value may have been transferred without its date context. The 1900 and 1904 systems differ by 1,462 days for the same calendar date.

How can I get the number of days between dates? Subtract the earlier date from the later date, or use DAYS with the end date first and start date second. Decide whether your count should measure elapsed days or include both endpoint dates; inclusive counts generally add one.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.