Date Serial Converter
Convert between calendar dates and Excel 1900-system serial numbers. Check the serial-60 leap-year exception alongside date formula examples.
How it works
In Excel or Google Sheets, a number like 45443 is a date in disguise — format the cell as a Date (Ctrl+1 ▸ Date) or convert it with =TEXT(A2,"yyyy-mm-dd"); 45443 is May 31, 2024.
Select the cells showing five-digit numbers and press Ctrl+1.
Choose Date (or a custom format like yyyy-mm-dd) and confirm.
For a text version, use =TEXT(A2,"yyyy-mm-dd"); for the reverse, =DATEVALUE("2024-05-31").
Decode a single serial instantly with the converter above.
What this does
Excel stores every date as a serial number: the count of days since January 1, 1900 (serial 1). Times are the fraction after the decimal point — 45443.5 is noon. The "date" you see is only a number format. That is why date math works (=B2-A2 is plain subtraction), why a date pasted into a General cell shows as a five-digit number, and why serial 60 is a famous oddity: it represents February 29, 1900, a day that never existed, kept for Lotus 1-2-3 compatibility.
A worked example
A CSV import shows 45443 where a date belongs. Select the column, press Ctrl+1, choose Date — the cell now displays 31/05/2024. The stored value never changed; only the format did. To get a text date instead, =TEXT(A2,"yyyy-mm-dd") returns "2024-05-31". Serial numbers surface constantly — CSV exports, lookups returning 45443, dates pasted into General cells. Knowing they are formats, not values, turns a confusing five-digit number into a one-click fix.
Common mistakes
- Re-typing the dates by hand instead of changing the number format.
- Subtracting dates and formatting the result as a Date — a difference is a count of days; format it as Number.
- Mixing the 1900 system with files from old Mac Excel (1904 system), which shifts every date by 1462 days.
- Expecting serials for dates before 1900 — Excel cannot store them as dates.
FAQ
Why does my date show as a number like 45443?
The cell has a General or Number format. The value is the date serial; apply a Date format and it displays as May 31, 2024.
What is Excel serial number 1?
January 1, 1900. Each following day adds 1; fractions are time of day.
Why does Excel think 1900 was a leap year?
Serial 60 is February 29, 1900 — a bug inherited from Lotus 1-2-3 and kept deliberately for file compatibility.