sethserver.com Subscribe
None

Excel Serial Date Converter and the 1900 Leap Year Bug

By Seth Black • Updated: September 17, 2026

Programming · 9 min read

Like this kind of writing? Get one email a week with notes on startups, AI, and the occasional strong opinion about Python: subscribe to the newsletter.

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.

Date system
Serial number to date
Try:
Excel shows
Date to serial number
Excel-only dates work too: 1900-02-29 and 1900-01-00.
Serial number
Same conversion in code

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:

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:

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:

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.

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:

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")

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

Share this post
Newsletter

One email, once a week.

Notes on databases, systems, and the occasional strong opinion about Python. No spam, unsubscribe anytime.

Seth Black
Written by

Seth Black

Engineer and founder based in Texas. Writes about databases, AI, and running things in production. Embeds in small teams as lead engineer.

More from Programming

View all →
Programming

What's the Differences Between Relational, NoSQL, Vectors, and Caches

Apr 14, 2026
Programming

Git and Source Control in AI Land

Apr 14, 2026
Programming

Domains, DNS, and why it feels fragile

Apr 14, 2026