How-to
How to Convert Excel Serial Numbers to Dates
45472 is 29 June 2024 to Excel. Convert serial numbers from exports, APIs and libraries into real dates, and know the 1900 leap-year bug and the 1904 system.
The problem
“I read a spreadsheet with a script and the date column came back as numbers like 45472.5 instead of dates.”
Why you get numbers instead of dates
Excel does not store dates as text. A date cell holds a serial number: the count of days since the start of its date system, with the time of day as a fraction. In the usual 1900 system, 45472 is 29 June 2024 and 45472.75 is 6 PM that day. What you see in Excel is a display format laid over that number.
Anything that reads the cell without its display format gets the bare number:
- a script or library reading an
.xlsxor.xlsfile without date handling; - a cell copied into a system that pastes values, not formats;
- an API or export that serialises the cell’s value;
- a CSV saved from a column that was formatted as General or Number.
The number is not wrong. It just needs reading in the right date system.
Fix it on Date And Time Converter
- Type the serial into the converter and choose Excel serial (1900 date system) as the source format. Serials are never detected automatically: a five-digit number could be anything.
- If the workbook came from an old Mac version of Excel and the result is four years and a day too early, choose the 1904 date system instead (see dates four years off).
- Choose the target format, such as
YYYY-MM-DDorYYYY-MM-DD HH:mm:ss. A serial has no time zone, so the converter keeps the clock as written; set a source zone only if you know where the spreadsheet’s times were recorded. - For a whole column of serials in a CSV, use the file workspace with the same source format; its date-system control picks 1900 or 1904 for the whole column.
- Input
45472.5read as Excel serial (1900 date system)- Result
2024-06-29 12:00:00YYYY-MM-DD HH:mm:ss · YYYY-MM-DD HH:mm:ss · clock kept as written · no timezone conversion
A whole column?
Open the CSV in the file workspace: pick the column, the formats and the zones, and the first 8 rows are converted at once, with counts for the whole file. The file is read in your browser and never uploaded. Downloading the converted file is part of Pro ($9.99/month).
In Excel, Sheets or code
In Excel or Google Sheets, the fix is a format, not a formula: select the column and apply a date format (Format Cells › Date, or Format › Number › Date time). To get text instead, use =TEXT(A2,"yyyy-mm-dd").
In code, count days from 30 December 1899 (valid for every date from 1 March 1900):
# pandas
pd.to_datetime(df["date"], unit="D", origin="1899-12-30")
// JavaScript (the result is the wall clock, written as UTC)
new Date(Date.UTC(1899, 11, 30) + serial * 86400000)
# R
as.Date(serial, origin = "1899-12-30")
-- SQL Server
SELECT DATEADD(day, 45472, '1899-12-30'); Better still, let the library read dates as dates: openpyxl returns datetime objects for cells with a date format, and pandas.read_excel converts such columns itself. You see serials when the cells lack a date format, or when the reader is told not to convert.
Pitfalls
- The 1900 leap-year bug. Excel counts a 29 February 1900 (serial 60) that never existed. Serials 1 to 59 (January and February 1900) are one day off with the 1899-12-30 origin; real data rarely goes back that far, but birth-date and historical sheets can.
- The 1904 system. The same serial is four years and one day later in a 1904 workbook. Know which system the source workbook used.
- Fractions and rounding. One second is 1/86400 of a day, about 0.0000116. A serial rounded to a few decimals in an export loses seconds;
45472.5is exactly noon,45472.33is 7:55:12 AM, not 8:00.
Questions
Why do formulas start from 30 December 1899?
Excel numbers 1 January 1900 as day 1 but also counts a 29 February 1900 that never existed, a choice kept for compatibility with Lotus 1-2-3. From 1 March 1900 on, a serial is the number of days since 30 December 1899.
Why does the converter not detect serial numbers?
A number such as 45472 could be many things: a count, an ID, a serial in either date system. You choose “Excel serial (1900 date system)” or the 1904 one, and the result says which reading it used.
Does a serial number have a time zone?
No. It is a calendar date and a clock time with no zone, so the converter keeps the clock as written unless you name the zone the spreadsheet was made in.