I have a love-hate relationship with Excel. Mostly hate, but also love. The love part is that it's everywhere, so if you build software that touches business data, you don't get to pretend it doesn't exist.
The hate part is that Excel has been lying about dates since the 80s, and I found out the hard way in the early 2000s while writing C++ finance software at Cingular Wireless. I was debugging date mismatches between my code and Excel exports, wondering why certain calculations were off by exactly one day. I spent longer on that than I want to admit. I wasn't losing my mind. Excel was doing Excel things.
Excel Serial Date Converter
If a spreadsheet handed you a number like 45658 where a date should be, paste it below. It goes both ways, handles the 1900 and 1904 date systems, and does it the way Excel actually does it, fake leap day and all.
The rest of this post is why serial 60 is a day that never happened.
Excel Dates: A Tiny Lie That Never Died
Excel stores dates as serial numbers. January 1, 1900 is 1. January 2 is 2. Keep counting and you get a nice, sortable timeline. Simple idea.
The problem is the base calendar is wrong on purpose.
Back in the 80s, Lotus 1-2-3 treated 1900 as a leap year. It wasn't. Leap years divisible by 100 need to also be divisible by 400. So:
- 2000 = leap year
- 1900 = not a leap year
Lotus got it wrong. Microsoft copied the behavior so spreadsheets would match. Compatibility won. Correctness lost. That means Excel accepts February 29, 1900 as a real date. It will happily label it as a specific day of the week, even though the date never happened.
The practical effect: every serial number from March 1, 1900 onward is one higher than a true count of days from January 1, 1900. The cell can look fine, but the number underneath is shifted. That's why code that converts Excel serials counts from December 30, 1899 instead of December 31, and why anything before March 1, 1900 comes out a day off. That shift is what bites you when you export, parse, compare, or do math across systems.
You might think this doesn't matter. Nobody's working with dates from 1900 anyway, right? Wrong. The bug matters because Excel dates don't stay inside Excel. They get exported into databases, CSVs, APIs, finance models, and scientific datasets. Once that off-by-one lands in a pipeline, it sticks around.
The Auto-Conversion That Wrecked Science
Then there's Excel's other favorite trick: it auto-converts strings into dates even when you didn't ask.
This isn't theoretical. A 2016 Genome Biology study found around 20% of papers with Excel gene lists in their supplementary data had errors because Excel "helped" by converting gene names into dates.
Examples that make me want to close the laptop:
SEPT2becomes September 2MARCH1becomes 1-Mar
It got so bad that the HUGO Gene Nomenclature Committee renamed genes to avoid Excel breaking them. Excel didn't adapt. The naming system did.
Y2K: The Sliding Window Hack
Excel also has a "solution" for two-digit years. It uses a sliding window:
00to29means2000to202930to99means1930to1999
So "1/1/29" becomes Jan 1, 2029. But "1/1/30" becomes Jan 1, 1930. That's not a bug. That's a choice. It works right up until it doesn't, which is basically the Excel product philosophy.
International Dates: The Same String, Two Realities
And then we get the classic: regional date formats.
- US:
month/day/year - Many other places:
day/month/year
So 03/04/2023 can be March 4 or April 3. Excel picks based on locale and file settings. If you're on an international team, this is how you get "correct-looking" spreadsheets with wrong numbers.
This is exactly why I only use UTC for storing timestamps. Display in local time if you must, but never trust regional formats in your data layer.
"AI-Powered" Excel Won't Save You
Microsoft has added AI-ish features to Excel. It can detect date columns, suggest charts, sometimes recognize formats, and generally try to be helpful without actually fixing anything.
None of that fixes the root problems:
- It doesn't remove the 1900 leap year lie.
- It doesn't stop auto-conversion by default.
- It doesn't enforce strict typing because Excel refuses to grow up.
What I Do Instead (When I Can)
If I'm building a pipeline, I keep dates out of Excel until the last possible moment. I parse and validate in code.
Here's the boring, reliable version in Python:
import pandas as pd
df = pd.read_csv("data.csv", dtype={"gene": "string"})
df["date"] = pd.to_datetime(df["date"], format="%Y-%m-%d", errors="raise")
- I force types.
- I choose a format.
- I fail loudly.
Excel's default behavior is "accept everything, guess a bunch, and don't tell you what changed." That's fine for a quick budget. It's not fine for anything that needs to be right.
The real lesson isn't "Excel bad." It's that early design decisions stick. Microsoft chose compatibility over correctness in the 80s, and now they're stuck with it. You can't fix a bug that millions of spreadsheets depend on. At some point, the bug becomes the feature.
And yes, Excel will keep accepting February 29, 1900, because fixing it would break too many things.
-Sethers