Excel Date Systems: 1900 vs. 1904 and Safe Conversion
Excel’s 1900 and 1904 date systems assign different serial numbers to the same calendar date: for dates shared by both systems, the numbers differ by 1,462 days. To convert safely, change the workbook’s date system only when appropriate, or adjust date serials by 1,462 days when moving date values between workbooks with different systems.
What the two date systems mean
Excel stores dates as serial numbers, not as a special kind of cell value. A date-formatted cell displays a serial number as a calendar date; a number-formatted cell reveals the underlying value. Times are stored as fractions of a day, so a value such as 0.5 represents noon.
The 1900 system counts from the beginning of 1900, with serial 1 representing January 1, 1900. The 1904 system starts earlier: serial 0 represents January 1, 1904. Consequently, the same modern date has a serial number 1,462 higher in the 1900 system than in the 1904 system.
For example, January 1, 2024 is serial 45,292 in the 1900 system and 43,830 in the 1904 system. The dates are identical; only their stored numbers differ. The offset is 1,462 days, or four years and one day, because the interval includes a leap day.
Why Excel has both systems
The 1904 system originated with early Macintosh spreadsheet software. The 1900 system became the usual choice for Windows versions of Excel. Excel retains both systems partly for compatibility with older workbooks and software; a workbook’s selected system affects how its serial numbers are interpreted.
There is also a historical quirk in the 1900 system. To remain compatible with an earlier spreadsheet program, Excel treats 1900 as if it had a February 29, even though it was not a leap year. Excel therefore recognizes the nonexistent date February 29, 1900 as serial 60. This exception is one reason not to build date conversions by assuming the serial number is simply the number of real elapsed days from January 1, 1900.
What changes when you switch systems
The date-system setting belongs to the workbook. In desktop Excel, it is generally found under File > Options > Advanced, in the section for calculating the workbook. The option is labeled Use 1904 date system; the location and wording can vary somewhat by Excel version or platform.
Changing this setting changes how Excel interprets date serials in that workbook. Excel does not necessarily rewrite every stored number to preserve the displayed dates. As a result, existing dates can appear about four years and one day earlier or later after a switch. Check dates, formulas, charts, and linked data after changing the setting, and avoid switching it casually in an established workbook.
The setting does not mean that every number in the workbook is a date. A value such as 45,292 might be a date serial in one cell and an ordinary quantity in another. Do not add or subtract 1,462 from an entire worksheet or from every numeric value.
How to convert dates safely
First identify the source and destination workbook’s date systems. Then decide whether you are changing a workbook’s setting or transferring date values. These are different operations: changing a setting affects interpretation throughout a workbook, while converting transferred values adjusts only the date serials being moved.
- Make a copy. Preserve the original workbook so you can compare results and recover if dates shift unexpectedly.
- Check both systems. In each workbook, inspect the date-system option in Excel’s advanced calculation settings. Do not assume that two workbooks use the same system.
- For a cross-system transfer, adjust date serials. When transferring actual date values from a 1904-system workbook to a 1900-system workbook, add 1,462. For the reverse direction, subtract 1,462. Apply the adjustment only to date serials.
- Keep times intact. A date-and-time value includes a fractional part for the time of day. Adding or subtracting the whole number 1,462 preserves that fraction.
- Verify the result. Format converted cells as dates or dates with times, and compare representative values with the source. Check formulas and ranges that refer to those cells.
For a simple test, if a 1904-system cell contains 43,830 for January 1, 2024, adding 1,462 gives 45,292, the corresponding 1900-system serial. If converting an entire column, use a helper column or a carefully controlled formula, confirm the results, and replace the originals only after validation. Copying and pasting between workbooks can sometimes handle date-system differences automatically, but behavior can depend on the transfer method and application; verify rather than relying on it.
Common problems and misconceptions
- Dates shift by about four years. This often happens after changing the workbook setting or moving raw serial values between workbooks that use different systems without conversion.
- A date becomes a large number. The value may still be a valid serial; the destination cell may simply have a General or Number format. Apply a date format to display it as a date.
- Only some dates change. A workbook can contain formulas, text that looks like a date, and ordinary numbers alongside genuine date serials. A serial adjustment will not correctly convert all of these indiscriminately.
- Text dates do not respond as expected. A date stored as text is not necessarily a serial number. Convert it to a true date value first, using a method appropriate to its format and regional date conventions.
- Leap-day behavior seems unusual. The 1900 system’s compatibility treatment of February 29, 1900 is an intentional historical exception. Avoid using dates near that early boundary for general-purpose elapsed-day calculations across systems.
Also distinguish a displayed date from the formula result. A formula may return a correct serial while the cell format makes it look wrong, or it may return a number that is being mistakenly interpreted as a date. Check the formula bar, cell format, and workbook setting before editing values.
Choosing a system and checking compatibility
For a new workbook, use the default system for the Excel environment and the people or applications that will use the file, unless a specific compatibility requirement calls for the other system. Keeping related workbooks on the same date system reduces the chance of errors when copying values or exchanging data. When working with older files, confirm the setting rather than changing it simply to match a preference.
When exporting to other spreadsheet applications, databases, or text files, remember that the receiving system may represent dates differently. A CSV file, for example, stores text and values but does not preserve Excel workbook settings or cell formats. Prefer exporting clearly formatted date text when appropriate, or document the serial convention if raw serial numbers are being exchanged.
Frequently Asked Questions
How many days apart are Excel’s 1900 and 1904 date systems?
For dates that both systems represent, the serial numbers differ by 1,462 days. Add that amount when converting a 1904-system serial to the 1900 system; subtract it in the opposite direction.
Which Excel date system should I use?
Usually, keep the system already used by the workbook and follow the needs of the people and software that share it. For a new workbook, the default is generally suitable unless compatibility requirements specify otherwise.
Will changing the date-system setting convert my existing dates?
Do not assume it will preserve their displayed calendar dates. The setting changes how serials are interpreted, so existing dates can shift. Make a copy and verify the workbook before and after any change.
Does the 1,462-day adjustment affect times?
Adding or subtracting a whole number of days leaves the fractional part of a serial unchanged, so the time of day is preserved. Confirm that the cells contain real date-time values, not text.
Why does Excel show a number instead of a date?
Excel may be displaying the underlying serial because the cell is formatted as General or Number. Apply a date or date-and-time format; if the value is text, formatting alone may not convert it.
Why does Excel recognize February 29, 1900?
The 1900 date system includes this nonexistent date to preserve compatibility with an earlier spreadsheet program. It is a special-case behavior, not evidence that 1900 was a leap year.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.