Why your dates are off by exactly 4 years, or exactly 1 day
A cell showing a date is not storing a date. It's storing a plain number — a day count from an epoch — plus a display format. Two things can go wrong with the epoch, and they produce two very different symptoms.
of real .xlsx files in the FUSE corpus declare the 1904 date system in workbookPr — about 1 in 45. (Enron: 0.21%, 33 of 15,871 — an older, more Windows-homogeneous corpus.) Every one of them silently shifts every date by 1,462 days against a reader that assumes the 1900 default.
Bug 1: the 1904 flag mismatch (the one that actually bites)
xl/workbook.xml carries a <workbookPr date1904="1"/> flag. Absent or "0", day 1 is January 1, 1900. Set to "1" (a holdover from early Mac Excel, which used the Mac epoch to dodge the 1900 bug below), day 0 is January 1, 1904. Same raw number, two different real-world dates, and nothing in the number itself tells you which system it's in — only the flag does.
Reproduce it
Two fixtures, identical except for one attribute, both holding the same raw serial number in cell A1:
$ diff <(unzip -p a.xlsx xl/workbook.xml) <(unzip -p b.xlsx xl/workbook.xml) < <workbookPr/> --- > <workbookPr date1904="1"/> $ soffice --headless --convert-to csv a.xlsx --outdir out_a # 1900 system $ soffice --headless --convert-to csv b.xlsx --outdir out_b # 1904 system $ cat out_a/a.csv 40000,2009-07-06 $ cat out_b/b.csv 40000,2013-07-07
Same byte, 40000, four years and one day apart — 1,462 days, which is the fixed gap between the two epochs for every date, not just this one (the two systems tick at the same rate; only the zero point differs). Our own intake CLI reads the flag directly off the same file:
$ intake b.xlsx out/
$ grep date_system out/_manifest.json
"date_system": "1904",Bug 2: the 1900 leap-year bug (the one everyone's heard of)
Lotus 1-2-3 treated 1900 as a leap year by mistake. Excel replicated the bug on purpose, to keep Lotus-exported numbers importing correctly, and froze it into the file format permanently: serial number 60 is a fictitious February 29, 1900, that never existed. It's confined to a 60-day window — every real date from March 1, 1900 onward is unaffected, ticking at exactly the same rate as a correct calendar would.
Nobody actually loses data to this in a modern workbook — nobody has legitimate financial data from January 1900. Its real relevance is historical: it's the reason the 1904 system exists at all (early Macs sidestepped the ambiguity with a different epoch), and getting the 60-day window right is exactly the kind of fencepost a general-purpose date engine has to encode once and never think about again, because it's frozen into every .xlsx file's 1900 date system whether the file was written yesterday or twenty years ago.
One practical warning if you are building or testing a date engine of your own: LibreOffice cannot be used as a reference for this window. Reading the same .xlsx, it has no phantom day at all, so its serials for every date before March 1, 1900 sit one day off Excel’s. We measured it:
| Formula | LibreOffice | Excel |
|---|---|---|
DAY(59) | 27 | 28 |
DAY(60) | 28 | 29 (the phantom day) |
DAY(61) | 1 | 1 |
MONTH(60) | 2 | 2 |
DATE(1900,2,29) | normalises to Mar 1 | 60 |
DATE(1900,3,1)-DATE(1900,2,28) | 1 | 2 |
The LibreOffice column is measured — a hand-built fixture converted with soffice --headless --convert-to csv. The Excel column follows from Microsoft’s own documentation of the leap-year assumption, which also notes that “the WEEKDAY function returns incorrect values for dates before March 1, 1900” — so even Excel does not claim this window is coherent, only that it is frozen.
The trap is that LibreOffice is the obvious oracle to reach for when you have no Excel to hand, and it disagrees with Excel in exactly the 60-day window you are trying to get right — quietly, with plausible-looking dates, in the one place a test would tell you you had succeeded. It is a perfectly good differential oracle everywhere else; here it will confirm a wrong answer.
Excel vs. most tools
Excel
Always reads its own date1904 flag correctly — it wrote the file.
Naive readers
Assume the 1900 system unconditionally and never check workbookPr/@date1904 — silently shifting every date in roughly 1 of every 45 real files by exactly four years, with no error, no warning, and a result that still looks like a plausible date.
Basis threads a date1904: bool parsed straight from workbookPr through every date function in the engine — DATE, YEAR/MONTH/DAY, EDATE, EOMONTH, YEARFRAC, NETWORKDAYS, TODAY — so a file's own declared epoch is what gets used, not an assumption.
Migrated a workbook from old Mac Excel, Numbers, or a third-party exporter? Check which date system it actually declares before trusting a date-diff formula against it.