Excel Date as Number: Excel Serial Dates Explained

Yes. Excel stores dates as serial numbers: the whole-number part counts days, and any decimal part represents a time within the day. To see a date cell’s serial value in Excel for Microsoft 365 on Windows, select it and choose Home > Number format dropdown > General; the date will display as a number instead.

What an Excel date number represents

In Excel’s standard 1900 date system, January 1, 1900, has serial value 1, January 2 has value 2, and each later calendar day increases the number by one. For example, January 1, 2024, is serial number 45292. That number is not a code unique to that date: it is a count of days from Excel’s starting point.

Excel’s 1900 system includes a historic compatibility quirk. It treats 1900 as if it were a leap year, even though the Gregorian calendar says it was not. As a result, Excel assigns serial 60 to the nonexistent date February 29, 1900. Dates from March 1, 1900, onward reflect this extra day. The quirk was retained for compatibility with earlier spreadsheet software, so it can matter when comparing very old dates or exchanging serial values with other systems.

There is also a 1904 date system, which some older Mac workbooks used. In that system, January 1, 1904, is serial 0. The same modern calendar date has a serial value 1462 lower in the 1904 system than in the 1900 system. Excel stores the chosen date system with the workbook, so check it before interpreting raw serials from another file.

How dates and times combine

A date-and-time value uses the integer for the date and a fraction for the time. One day is 1, so noon is 0.5, 6 a.m. is 0.25, and 6 p.m. is 0.75. A value such as 45292.5 therefore represents noon on January 1, 2024, in the 1900 system. Excel can display that value as a date, a time, both, or a plain decimal depending on the cell’s number format.

The fractions are proportions of a 24-hour day, not counts of minutes. For instance, 30 minutes is 30 divided by 1440, or about 0.0208333. Formatting can hide the fraction or show it as hours and minutes, but the underlying number remains the same. This is why a cell that looks like a date can participate in arithmetic, and why a time-only entry can be represented by a number less than 1.

To see the numeric value of January 1, 2024, with no cell inputs required, enter =DATE(2024,1,1). In a workbook using the 1900 date system, the result is 45292. With no cell inputs required, =TIME(12,0,0) returns 0.5, which Excel can format to display as 12:00 PM.

How to show a date as a number

Changing the number format changes how Excel displays a value; it does not convert the date into a different underlying value. In Windows Excel, select the cell and use the Home tab’s Number format dropdown to choose General or Number. General normally displays the integer serial, while Number may show decimal places as well. You can also press Ctrl+1 to open Format Cells and select a category. On Mac, use Command+1 for Format Cells; the ribbon’s number-format controls are also available.

If the cell contains a date and time, General or Number may show a decimal serial. If you only want to inspect the date portion, choose a date format that omits the time; if you need the time portion, choose a time format. The common Windows shortcut Ctrl+Shift+~ applies the General format. On Mac, keyboard shortcuts can vary with the Excel version and keyboard layout, so the ribbon or Format Cells dialog is the dependable alternative.

Use General when you want to see the serial number without deliberately adding decimal places.
Use Number when you want to inspect or control the decimal portion of a date-time value.
Use a date or time format when you want the cell to remain readable as a calendar date or clock time.

Using serial dates in calculations

Because dates are numeric values, subtracting one date from another gives the number of elapsed days. With a start date in A2 and an end date in B2, enter =B2-A2 in C2. If A2 contains April 1, 2024, and B2 contains April 10, 2024, the result is 9. The formula counts the difference between the dates; if you need to count both the start and end dates inclusively, add 1 to that result.

Adding a whole number to a date moves it forward by that many days. For example, if A2 contains April 1, 2024, then =A2+7 returns the serial for April 8, 2024. The result may appear as a number if the formula cell is formatted as General, so apply a date format when you want to see the calendar result. Subtracting a number similarly moves a date backward.

Time calculations follow the same principle but use fractions of a day. Adding 0.5 to a date-time value advances it by 12 hours. For work schedules, elapsed-time calculations can exceed 24 hours; a format such as [h]:mm displays a total duration rather than rolling the hours back to zero after each day. If a calculation crosses midnight, subtracting the start time from the end time without accounting for the date can produce a negative result, so include the dates or otherwise handle the rollover.

Serial numbers versus text that looks like a date

A value that looks like 4/10/2024 is not necessarily a real Excel date. It may be stored as text, particularly if it was imported from a website or CSV file, or if the source used a date order Excel did not recognize. A genuine date is numeric and responds to date arithmetic; text generally does not behave like a date in calculations and may remain left-aligned under the usual formatting defaults. Alignment alone is only a clue, not a definitive test.

To check, select the cell and change its format to General. If it displays a serial number, it is likely a numeric date value. If it still shows the original date characters, it may be text. You can also test whether subtracting a known date works, or inspect the formula bar and cell contents. Be cautious when converting imported dates: 03/04/2024 can mean March 4 or April 3 depending on the source’s regional convention. Confirm the intended order before converting a column.

Formatting text as a date does not necessarily turn it into a numeric date. If Excel does not recognize the text, applying a date format will leave the text unchanged. Conversion may require correcting the source format or using a suitable data-import or conversion method. Once converted, check a few results against the original data rather than assuming every ambiguous entry was interpreted correctly.

1900 and 1904 date systems in shared workbooks

A mismatch between date systems can make dates appear 1462 days apart when serial values are transferred or interpreted incorrectly. This is especially relevant when copying raw numbers between workbooks, linking to an older workbook, or moving data through software that does not preserve Excel’s workbook date-system setting. A value by itself is not enough to identify its intended calendar date; you need to know which system produced it.

On Windows, the setting is in File > Options > Advanced, under the section titled When calculating this workbook; the option is Use 1904 date system. On Mac, the corresponding workbook setting is available through Excel > Settings (or Preferences in some releases) > Calculation. Changing the setting can shift the displayed dates of existing serial values, so do not toggle it casually in a workbook with established dates. When receiving a file, verify the displayed dates and date system before making changes.

Common problems and what they mean

The cell shows a large number. The value may be a valid date displayed with General or Number format. Apply a date format if you want a calendar display.
The cell shows ######. The column may be too narrow for the formatted date, or the value may be a negative date-time that Excel cannot display with the selected date system. Widen the column first, then check the value and calculation if the marks remain.
The date does not change when formatted. It may be text rather than a numeric date. Check the source and regional date order, then convert it deliberately.
A date shifts after copying between files. The workbooks may use different date systems, or the number may have been transferred without its intended context. Compare the workbook settings and the displayed calendar dates.
A formula returns a decimal. The calculation may include a time fraction, or the result cell may use General or Number formatting. Choose a date/time display format that matches what the result represents.

Frequently Asked Questions

What number is January 1, 2024, in Excel?
In the standard 1900 date system, it is 45292. A workbook using the 1904 date system will use a value 1462 lower for the same date.

Why does Excel use numbers for dates?
Serial values let Excel calculate intervals, add or subtract days, and combine dates with times. The number format then displays the numeric value in a human-readable calendar or clock style.

Is 0.5 a date or a time in Excel?
By itself, 0.5 represents half a day, or 12 hours. If it is part of a larger serial such as 45292.5, it represents noon on the date represented by the whole-number part.

How do I convert an Excel date to a number without changing it?
Select the cell and change its number format to General or Number. This reveals the stored serial value while leaving the underlying date value unchanged.

Why does a date copied from another program show the wrong day?
Possible causes include a 1900-versus-1904 date-system mismatch or a different interpretation of an ambiguous text date such as 03/04/2024. Check the source format and workbook settings before correcting the data.

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.