Excel date serial numbers and the 1900 leap year bug

1. What an Excel date serial number is

Excel stores a date as a number, not as text. The cell may display 2026-09-10, but the value underneath is a count of days since a reference date. That number lets Excel subtract two dates to get a day count, or add a number to a date to get a later day. Formatting a cell as "General" reveals the number; formatting it as a date shows the same value as a date.

The trouble starts once the value leaves Excel. A CSV export, or another program reading the raw cell value without the date format, loses the formatting and keeps only the serial number. That is why a database extract or an API response sometimes shows 45123 where a date belongs.

2. The basic rules: two date systems

Excel counts days from one of two reference dates. The 1900 date system, the Windows default, sets 1 January 1900 as serial number 1. The 1904 date system, the old macOS default, sets 1 January 1904 as serial number 0. Microsoft's own documentation states that the same date, 5 July 2011, is serial number 40729 in the 1900 system and 39267 in the 1904 system, a constant difference of 1,462 days.

Current macOS Excel also defaults to the 1900 system now. Which system a given file uses can vary (the setting lives under Excel's File > Options > Advanced menu), so trying both and picking whichever gives a plausible date is a workable fallback.

3. The 1900 leap year bug: the rules inside this page

The 1900 date system carries a known bug. Excel wrongly treats 1900 as a leap year and counts a 29 February 1900 that never existed. So 1 January 1900 is 1, 28 February 1900 is 59, the nonexistent 29 February 1900 takes 60, and 1 March 1900 is 61. This page's src/lib/exceldate.ts computes exactly that sequence.

Microsoft deliberately does not fix this. Fixing it would shift every date in every existing Excel document back by one day, as Microsoft's documentation explains. So this page does not correct it either. Correcting it would make this tool disagree with real Excel, which is the wrong kind of correct.

The 1904 date system has no such bug. Counting from 1 January 1904 through the real Gregorian calendar means a real leap day, such as 29 February 2000, converts correctly.

A decimal fraction in the serial number is the time of day. .5 is noon, .25 is 6 AM. This page shows that time too, rounded to the nearest second.

4. Getting it right

Copy a whole date column from a spreadsheet and paste it into the left box to convert every row at once. The line order is kept, so you can paste the result back next to the original column.

If you are not sure which system applies, convert one number under both and pick whichever gives a plausible date (within the last few years, say). The two systems differ by exactly 1,462 days, so knowing one tells you the other.

A line that does not match the expected format is shown in red. Dates are read only as year-month-day with hyphens, like 2026-09-10; other layouts such as 2026/09/10 or 09/10/2026 are not supported.

5. A little deeper

Converting serial number 60 to a date shows a note that the day never existed. Excel itself displays that value as 2/29/1900, but that date is not one that ever occurred.

Excel's WEEKDAY function only returns a wrong day of the week for dates before 1 March 1900. Everything outside that range, which is almost every real-world business date, is unaffected.

Other tools that handle dates (Python's pandas, R, and most others) generally do not reproduce this bug and compute the real Gregorian calendar instead. Converting the same Excel number in such a tool can therefore differ by a day, but only within the pre-March-1900 range.

6. Where people get stuck

If a converted date is a day earlier or later than expected, the wrong system is probably selected. Try switching between the 1900 and 1904 buttons.

A very small number (an early-1900s date) may fall inside the 1900 leap year bug's range. This page is built to match Excel exactly there too.

If a date line turns red, check the year-month-day order and the hyphens. To include a time, add a space after the date followed by hour:minute or hour:minute:second.