Skip to content
QuicklySorted

Excel date serial numbers explained

Excel stores every date as a number: the count of days from a fixed starting day. That is why a date sometimes appears as 45292, and why the same number can mean two different dates in two workbooks. This guide explains both of Excel's date systems, the leap year mistake it keeps on purpose, how times are stored, and how to convert in Excel, Google Sheets, Python, and JavaScript.

Checked against the sources below on .

How Excel stores a date

In Excel's default system, called the 1900 date system, 1 January 1900 is day 1, 2 January 1900 is day 2, and so on. 1 January 2024 is day 45292. Excel shows that number as a date only because the cell has a date format. Change the format to General and the number is what you see.

Storing dates as numbers is what makes date arithmetic work. Subtracting one date from another gives the days between them, and adding 30 to a date gives the date 30 days later. The largest date Excel accepts is 31 December 9999.

Serial numbers in the 1900 date system
Serial numberDateNote
11 January 1900The first date Excel accepts
5928 February 1900
6029 February 1900A date that never existed
611 March 1900From here, serials match a true day count
255691 January 1970The start of Unix time
365261 January 2000
407295 July 2011Microsoft's own example
452921 January 2024
295846531 December 9999The last date Excel accepts

The 29 February 1900 that never happened

1900 was not a leap year. A year divisible by 100 is a leap year only when it is also divisible by 400, so 2000 was a leap year and 1900 was not. Excel's 1900 date system counts 29 February 1900 anyway, as serial number 60.

Microsoft documents this as a known issue. Lotus 1-2-3, the leading spreadsheet at the time, treated 1900 as a leap year, and Multiplan and Excel did the same so they could use the same serial numbers and open Lotus files with the right dates. Fixing it now would move almost every date in existing workbooks by a day, so it stays. Read Microsoft's explanation.

In practice this matters only for dates before 1 March 1900. From that day on, every serial number is a true count of days from 30 December 1899, which is why other programs use that day as day zero and still agree with Excel. Before it, Excel's serial numbers are one higher than a true count, and Microsoft notes that the WEEKDAY function gives wrong answers for those dates.

The 1904 date system

Older versions of Excel for Mac used a second system, the 1904 date system, which counts the days since 1 January 1904. In it, 1 January 1904 is day 0. The same date is always 1,462 days lower than in the 1900 system, which is four years and one leap day: 1 January 2024 is 45292 in the 1900 system and 43830 in the 1904 system.

All newer versions of Excel use the 1900 system by default, but a workbook keeps the system it was made with. On Windows, the setting is under File, Options, Advanced, When calculating this workbook: Use 1904 date system. On a Mac, it is under Excel, Preferences, Calculation.

The danger is moving dates between workbooks that use different systems. If the dates are not converted on the way, every one of them moves by four years and one day. Newer Excel for Mac converts pasted dates unless that option is turned off, but dates copied as part of a chart, or read by another program, are not converted for you.

How times are stored

The time of day is the fraction after the decimal point: the share of the day that has passed. Noon is 0.5, 6:00 in the morning is 0.25, and 45292.75 is 6:00 in the evening on 1 January 2024. One hour is 1/24, about 0.041667, and one minute is 1/1,440 of a day.

A time typed on its own, such as 08:30, is stored as a fraction with no whole days: 0.354166 and so on. Formatted as a date, it shows as day zero of January 1900, which is Excel's way of saying no date at all.

Because times are fractions of a day, a total of more than 24 hours rolls over unless you format it as [h]:mm, which keeps counting the hours. For shifts that pass midnight, the Hours Worked Calculator works out each day and the weekly total for you.

Converting in Excel and Google Sheets

Formulas for Excel and Google Sheets, with the date or number in cell A1
To do thisUse this formula
Make a date from its parts=DATE(2024,1,1), which is 45292 when formatted as General
Show a serial number as a dateFormat the cell as a date, or =TEXT(A1,"yyyy-mm-dd")
Turn a date stored as text into a real date=DATEVALUE(A1)
Unix seconds to an Excel date=A1/86400+DATE(1970,1,1)
An Excel date to Unix seconds=(A1-DATE(1970,1,1))*86400
Days between two dates=B1-A1

The Unix formulas treat the Excel date as UTC, because Excel dates have no time zone. Watch the year in DATE too: Excel and Google Sheets add 1900 to a year below 1900, so =DATE(24,1,1) is 1 January 1924, not 2024. To check a Unix time on its own, paste it into the Unix Timestamp Converter.

Google Sheets stores dates the same way, but counts the days since 30 December 1899 and has no 29 February 1900. Its serial numbers match Excel's from 1 March 1900 on, so the same formulas give the same results for any modern date.

Converting in code

Python, with pandas
import pandas as pd

pd.to_datetime(45292, unit="D", origin="1899-12-30")
# Timestamp('2024-01-01 00:00:00')
JavaScript
const serial = 45292;
const date = new Date(Date.UTC(1899, 11, 30) + serial * 86_400_000);

date.toISOString(); // "2024-01-01T00:00:00.000Z"

Both use 30 December 1899 as day zero, which gives the right date for every serial number from 61, that is 1 March 1900, onward. For a workbook in the 1904 system, use 1 January 1904 as day zero instead. Libraries that read .xlsx files usually check the workbook's date system for you; values copied out as plain numbers carry no such setting.

Common problems and their fixes

  • A column of dates shows numbers such as 45292. The cells are formatted as General or Number. Format them as a date.
  • Dates are four years and one day out. They came from a workbook in the other date system. Add or take away 1,462.
  • Dates will not sort or calculate. They are stored as text, often after an import. Convert them with DATEVALUE, or with Data, Text to Columns.
  • A date before 1900 stays as text. Neither date system goes back before its starting day, so earlier dates can only be stored as text.
  • A CSV file opens with the day and the month swapped. Excel read it in your computer's regional date order. Import it with Data, From Text/CSV, or write dates as YYYY-MM-DD.

Excel Date Converter

Convert dates to Excel serial numbers and back.

Open the Excel Date Converter

Questions

45292 is how Excel stores 1 January 2024: the number of days since its starting day. The cell is formatted as a number. Format it as a date and the date appears.

In the 1900 system, serial 1 is 1 January 1900. Because Excel also counts a 29 February 1900 that never happened, other programs use 30 December 1899 as day zero so their numbers match Excel's from March 1900 on. In the 1904 system, 1 January 1904 is day 0.

Format the cell as a date, or paste the number into the Excel Date Converter, which also handles the 1904 system and the time of day.

1,462 days, which is four years and one day. Add 1,462 to a 1904 serial number to get the 1900 serial number for the same date.

Sources

Missing a tool?

Tell us what you wanted to do. Your ideas decide which tools we build next.

Suggest a tool on wisemensoft.com