Excel Date Value: How Serial Dates Work

How-to basics · Updated November 18, 2026

An Excel date value is a number that represents a calendar date; Excel usually displays that number as a date because the cell has a date format. In the standard 1900 date system, January 1, 1900 is serial value 1, and each following day adds 1, so dates can be sorted, subtracted, and used in calculations.

What an Excel date value means

Excel does not store an ordinary date as month, day, and year text. It stores a numeric serial value and applies a number format to show the value in a familiar form. For example, the underlying value for January 1, 2024 in a workbook using the 1900 date system is 45292. If the cell uses a date format, Excel displays a calendar date; if it uses General or Number formatting, it may show the serial instead.

This distinction explains why changing a cell’s format can make a date appear to turn into a large number without actually changing the date value. The number is still there; only its presentation changed. It also explains why two cells that look like the same date may behave differently: one might contain a real numeric date value, while the other contains text that merely looks like a date.

To see the underlying value, select a date cell and choose Home > Number Format, then select General or Number. To restore a date display, select a date format from the same menu. In Excel for Microsoft 365 on Windows, Ctrl+1 opens Format Cells; on Mac, Command+1 opens the formatting dialog.

How Excel’s date serial system works

In the 1900 date system, serial values count days from the beginning of 1900. The count advances by one for each day, including across months and years. This makes date arithmetic straightforward: the difference between two date values is the number of days between them, provided both cells contain actual date values.

For a concrete example, with a cell intended to hold a date value, the formula =DATE(2024,1,1) returns 45292 in a workbook using the 1900 date system. Format the result as a date and Excel displays January 1, 2024. DATE is useful when you want to build a date from separate year, month, and day numbers rather than depend on how a text entry is interpreted.

There is an important historical exception. To remain compatible with an old spreadsheet program, Excel treats 1900 as if it were a leap year. It was not: February 1900 had 28 days. Excel therefore recognizes the fictional February 29, 1900 as serial 60. In the 1900 system, serial 59 is February 28 and serial 61 is March 1. This compatibility quirk is rarely relevant to present-day dates, but it can affect calculations involving dates near the start of 1900 or comparisons with software that uses a different convention.

Time is stored as part of the same value

Excel represents time as a fraction of a day. The integer portion of a date-and-time value counts days, while the fraction represents the time within a day. Midnight is the start of the day; noon is 0.5, or half a day. One hour is 1/24 of a day, and one minute is 1/1440.

As a result, a date and time can be stored in one numeric value. For example, with a cell intended to calculate noon on January 1, 2024, =DATE(2024,1,1)+TIME(12,0,0) returns 45292.5 in the 1900 system. A date-and-time format can display that value as January 1, 2024, 12:00 PM. A date-only format hides the fractional time, but does not remove it.

Hidden time fractions can explain why two cells that appear to contain the same date do not compare as equal or why a duration calculation includes a fraction of a day. If a cell contains a timestamp and you need its date component only, use a date-only display format if the goal is just presentation. If you need to remove the time from the value for later calculations, with the timestamp in A2, =INT(A2) returns the whole-number date serial. The result should be formatted as a date to show it as a calendar date.

Why formatting changes what you see

A number format controls how Excel presents a value, not what the value is. For example, the same serial can appear as 1/1/2024, Jan 1, 2024, or a number, depending on the selected format and regional settings. Formatting a text string as a date does not necessarily convert it into a usable date value; it may remain text.

To choose a built-in date format in Excel for Microsoft 365 on Windows, select the cells, then use Home > Number Format and choose a date option. For more control, press Ctrl+1 on Windows or Command+1 on Mac, open the Number tab in Format Cells, and select Date or Custom. Common custom format components include:

  • d for a day number, such as 1; dd displays a leading zero, such as 01.
  • mmm for an abbreviated month name, and mmmm for the full month name.
  • yy for a two-digit year, and yyyy for a four-digit year.
  • h:mm for hours and minutes. When showing elapsed hours that may exceed 24, use a format such as [h]:mm.

Be cautious with short year formats: a display such as 1/2/24 is compact but can be ambiguous to a reader. A four-digit year is clearer in records that may be read or reused later. Also, a date format’s appearance can depend on the computer’s regional settings, so a workbook may display dates differently for different users.

Entering dates and converting text

When a user types a date, Excel tries to interpret it using the workbook and computer’s regional settings. An entry such as 3/4/2024 can mean March 4 or April 3 depending on the date convention in use. If the order is unclear, spell out the month or construct the date from separate year, month, and day values. Excel’s DATE function also normalizes valid month and day numbers; for example, a month number outside 1 through 12 rolls into another year, which can be useful but may also conceal mistaken input.

Imported data is a common source of date-like text. A CSV file, copied web table, or system export may contain strings that look like dates but are not numeric date values. A quick clue is that calculations or sorting behave unexpectedly, although alignment alone is not a dependable test because formatting can change alignment.

If A2 contains date text that Excel can interpret under the current regional settings, =DATEVALUE(A2) converts it to a numeric date serial. Format the result as a date to display it. DATEVALUE can fail or interpret a string differently when its month-day order is ambiguous or its text uses a language or format Excel does not recognize. For a more controlled conversion, split the source into year, month, and day fields and create a date with DATE, after checking that the source fields are in the expected order.

Text conversion is different from display formatting. Applying a date format to text does not guarantee conversion; likewise, copying a displayed date and pasting it elsewhere can preserve text rather than the original numeric value, depending on how it was copied and imported. If a cell behaves like text, convert it deliberately and verify that the result can be used in date arithmetic.

Calculations you can perform with dates

Once dates are numeric values, ordinary arithmetic can answer many everyday questions. With a start date in A2 and an end date in B2, =B2-A2 returns the number of days between them. For example, if A2 is January 1, 2024 and B2 is January 4, 2024, the result is 3. If either cell also contains a time, the result can include a decimal fraction.

With a date in A2, =A2+7 returns the date seven days later. Subtracting 7 moves seven days earlier. These calculations count calendar days, including weekends and holidays. They do not automatically calculate working days; that requires a workday-specific approach and an explicit decision about weekends and holidays.

Date arithmetic can also produce an unexpected display. If you subtract two dates and the result cell is formatted as a date, Excel may show a date-like result instead of the day count you intended. Set the result cell to General or Number to see a numeric duration. Conversely, if adding days produces a serial number, choose a date format to display the resulting calendar date.

Subtracting dates gives elapsed days, not a complete age or month-count convention. Months have different lengths, and a question such as “how many months?” needs a defined rule: completed calendar months, 30-day periods, or another convention. For deadlines and schedules, also decide whether the starting day counts as day zero or day one. Being clear about the rule matters as much as the cell formula.

1900 and 1904 date systems

Excel workbooks can use either the 1900 or 1904 date system. The 1900 system is the usual default for new Windows workbooks. The 1904 system starts its count at January 1, 1904, so the same calendar date has a different serial number in the two systems. For dates common to both systems, the serial values differ by 1462 days.

This matters when values are copied or linked between workbooks using different systems. A date serial transferred as a plain number can appear shifted by four years and one day, even though the original workbook displayed the intended date. Excel may handle some transfers between workbooks automatically, but do not assume every data-import or copying route will preserve the intended date. Check the displayed dates and the workbook settings when a batch of dates shifts consistently.

On Windows, the workbook setting is under File > Options > Advanced, in the section labeled When calculating this workbook; the option is Use 1904 date system. The setting applies to the workbook, so changing it can alter the interpretation or display of existing date serials. Mac versions of Excel can also work with the two date systems, but the preference location can differ by release. When sharing a workbook across platforms, inspect the setting in Excel rather than relying on a platform-specific assumption.

Common date-value problems

If a cell displays a number such as 45292 instead of a calendar date, the value may be valid and simply formatted as General or Number. Apply a date format. If it displays a string that remains unchanged after formatting, it may be text; convert it rather than only changing its appearance.

If a date appears several years off, check whether the workbooks involved use different date systems. If it is off by one day, look for a time fraction, an incorrect source date, or a conversion that interpreted day and month in the wrong order. A date that sorts in the wrong place among other dates is often text, although mixed formats and invalid data can complicate sorting too.

Excel’s normal date system is not designed for ordinary date calculations before January 1, 1900. Historic dates earlier than that may need to be stored as text or handled with a carefully designed alternative representation. Text preserves how a date is written but does not provide normal date arithmetic. Also, do not treat a blank cell as a reliable zero date: zero and blank can behave differently in formulas, and date formatting can make numeric zero look unusual.

For troubleshooting, check the cell’s format, inspect whether the value is numeric, confirm the workbook’s date system, and verify the source’s regional date order. Make a copy before changing a large imported column, especially if converting text, because a conversion can produce plausible but incorrect dates when the source convention is misunderstood.

Frequently Asked Questions

What number is January 1, 2024, in Excel? In the 1900 date system, its serial value is 45292. A cell formatted as a date displays the calendar date instead of that number.

Why is Excel showing a date as a number? The cell is likely using General or Number formatting. Select it and choose a date format. The numeric serial is the underlying value Excel uses for date calculations.

Are Excel dates stored as text? Usually, no: a recognized date is stored as a number. But imported or manually entered date-looking text can remain text, so it may not sort or calculate like a true date.

Why does subtracting two dates return a decimal? One or both cells probably include a time fraction. The decimal represents part of a day; format the cells to show time, or remove the time component if the calculation should use calendar dates only.

Why did dates change after copying between workbooks? The workbooks may use different date systems, or the data may have been interpreted using different regional date conventions. Check the 1900/1904 setting and verify ambiguous dates such as 3/4/2024 against the original source.

Related

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.