Excel Day Number: Serial Dates, Day of Month and Year

If A2 contains a valid Excel date such as June 15, 2024, and you want its day of the month, enter =DAY(A2); the result is 15. If by “day number” you mean Excel’s underlying date serial, format the cell as General instead: June 15, 2024 is serial number 45458 in Excel’s standard 1900 date system.

What “day number” means in Excel

The phrase can refer to several different values, and Excel has a different way to show or calculate each one. A calendar date such as June 15, 2024 can be represented as a serial number, the day within its month, the day within its year, or the day of the week. Those answers are different: for this example they are 45458, 15, 167, and Saturday.

Before choosing a formula, decide what the number needs to represent. For example, a payroll report may need the day of the month, a year-to-date analysis may need the day of the year, and a chart may need a weekday number. If you simply want to see how Excel stores a date, changing its display format is usually enough.

Excel date serial numbers

Excel stores dates as serial numbers so it can compare dates and perform calculations with them. In the default 1900 date system used by most Windows workbooks, January 1, 1900 has serial number 1. Later dates have larger numbers, with one whole number for each calendar day. For instance, June 15, 2024 has serial number 45458.

To inspect a date’s serial, select the cell and choose Home > Number Format > General. This changes how the value is displayed; it does not replace the date with a different underlying value. To show it as a date again, choose a date format from the Number Format list. You can also use Ctrl+1 on Windows to open Format Cells and choose a format. On Mac, Command+1 opens Format Cells; ribbon labels and dialog details can vary by Excel version.

Serial numbers make date arithmetic possible. If a cell contains a date, adding 1 advances it by one day, while subtracting two date values gives the number of days between them. A formatted date may hide that numeric value, so a result that looks like a date or a large number can often be corrected by choosing the appropriate number format.

Return the day of the month

Use the DAY function when you want the calendar day from 1 to 31. With a date in A2, enter =DAY(A2). If A2 contains June 15, 2024, the function returns 15. The result is a number, not text, so it can be sorted numerically or used in other calculations.

DAY does not return the day of the year or the weekday. For example, the 15th day of a month could be a Monday in one month and a Thursday in another. If a date is stored as text rather than recognized by Excel as a date, DAY may return an error or an unexpected result; see the troubleshooting section below.

Use a real date value in the source cell whenever possible. A date entered in a form Excel recognizes according to your regional settings is generally stored as a date serial, even though the cell displays a familiar calendar date. When creating a date from separate year, month, and day values, the DATE function can assemble those parts, but the input order and regional display settings should not be confused.

Calculate the day of the year

The day of the year counts from 1 on January 1 through 365, or 366 in a leap year. With a date in A2, use =A2-DATE(YEAR(A2),1,1)+1. For June 15, 2024, the answer is 167: 2024 is a leap year, and June 15 is the 167th calendar day of that year.

This calculation subtracts the serial for January 1 of the date’s year, then adds 1 because January 1 itself is day 1 rather than day 0. Excel’s DATE function handles leap years, so the same formula works for dates in both ordinary and leap years. It also avoids hard-coding a year, which would make the calculation wrong when the source date changes.

If A2 contains a timestamp as well as a date, the time is stored as a decimal fraction of a day. In that case, remove the time before calculating the day number. With a date and optional time in A2, use =INT(A2)-DATE(YEAR(A2),1,1)+1. For example, if A2 is June 15, 2024 at noon, the decimal time is discarded and the result remains 167.

Get a weekday number

For a numeric weekday, use WEEKDAY. With a date in A2, =WEEKDAY(A2,2) returns 1 for Monday through 7 for Sunday. June 15, 2024 was a Saturday, so this formula returns 6. The second argument, 2, specifies the Monday-first numbering scheme.

If you omit the second argument, Excel uses its default numbering: Sunday is 1, Monday is 2, and Saturday is 7. That default is a common source of mismatches when a report expects Monday to be the first day of the week. The weekday result is not an index in the month or year; it repeats every seven days.

To display a weekday name instead of a number, apply a date format that shows the day name, or use a text-formatting approach. Keep the original date value if you need to sort chronologically or calculate intervals; converting dates to labels alone can make later analysis less convenient.

Dates, times, and display formats

A date-time value combines a whole-number date serial with a fractional part for the time. For example, noon is half of a day, or 0.5. That is why a timestamp may appear as a decimal if you change its cell format to General. The date portion can be isolated with INT; the fractional portion represents the time.

Number formatting controls appearance, not the stored value. Formatting a date cell as General reveals its serial, while formatting a serial as a date makes Excel display a calendar date. A custom date format can also show only part of a value, such as the day number, while the full date remains stored. This distinction matters when sorting, filtering, and checking formulas: two cells that look identical may contain different dates or times.

For a column of dates, apply the same date format to the full range rather than converting values to text one by one. If a calculation should ignore times, use INT where appropriate. If times matter—for example, when measuring elapsed hours—do not remove the fractional part.

The 1900 and 1904 date systems

Excel workbooks can use either the 1900 or 1904 date system. The 1900 system is the default for current Windows workbooks. The 1904 system is found in some older workbooks and legacy Mac files. For the same modern calendar date, the serial numbers in these systems differ by 1,462 days. Consequently, copying raw date serials between workbooks that use different systems can make dates appear shifted.

Excel also preserves a historical compatibility quirk in the 1900 system: it treats 1900 as if it were a leap year, although it was not. Serial 60 therefore corresponds to the nonexistent date February 29, 1900. This behavior was retained for compatibility with earlier spreadsheet software. It is usually irrelevant for contemporary dates, but it can matter when analyzing very old dates or importing historical serial values.

On Windows, the workbook’s date-system setting is in File > Options > Advanced, under When calculating this workbook, where the option is labeled Use 1904 date system. Avoid changing it casually in an existing file: changing the system can alter how dates are interpreted or displayed. When combining data, check the workbook settings and confirm that the visible dates—not just copied serial values—match the source.

Common problems and how to check them

The formula returns #VALUE!. The source may be text that looks like a date, rather than a date value Excel recognizes. Check the cell’s contents and regional date conventions; for example, 4/5/2024 can be interpreted differently in different locales.
The answer looks like a date or a large serial. The result cell may have an unsuitable number format. Select it and choose General or Number for a numeric day result, or a date format when you want a calendar date.
The weekday is off by one. Confirm the WEEKDAY return type. Using 2 gives Monday as 1; leaving the second argument blank makes Sunday 1.
The result differs after copying between files. Check whether the workbooks use the 1900 and 1904 date systems. A 1,462-day difference can indicate that their serial systems do not match.
A date includes a time. A hidden time fraction can affect a calculation involving whole days. Use INT to discard the time only when the time is not needed.

When a result seems wrong, inspect both the formula and the cell format. A quick way to separate the two is to view the source date in General format, then return it to a date format and compare the calculated output as a Number.

Frequently Asked Questions

How do I show the Excel day number for a date? If you mean the stored serial, select the date cell and choose Home > Number Format > General. In the standard 1900 date system, June 15, 2024 displays as 45458. This changes the display, not the date value.

How do I get the day of the month in Excel? With a valid date in A2, enter =DAY(A2). If A2 contains June 15, 2024, the result is 15. This is not the day of the year or the weekday.

How do I find what day of the year a date is? With a date in A2, enter =A2-DATE(YEAR(A2),1,1)+1. June 15, 2024 returns 167. The calculation automatically accounts for leap years.

Why is my Excel date number different from someone else’s? The workbook may use a different date system. The 1900 and 1904 systems assign serials that differ by 1,462 days for the same modern date. Check the workbook’s date-system setting before comparing raw serials.

Does changing a date’s format change its day number? No. A date format changes what you see, not the underlying stored date serial. A date can display as a full calendar date, a weekday, or a serial while remaining the same value.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.