Cursed Excel: “1/2”+1=45660
(My co-worker) Andrew Farkas writes about the conflicting goals we wrestle with at Quadratic building a new spreadsheet:
- Maintain feature parity with Microsoft Excel
- Be good
His article is full of crazy “this is why things are the way they are” knowledge about Excel.
One of my favorite things about working here is that I get to reverse-engineer Excel, find strange quirks in its behavior, and decide what to do about them in Quadratic...Today you will gain a glimpse into the horrors I contend with, and then you too will live in fear of Microsoft Excel — not because you lack knowledge, but because you know too much.
As an example, here’s some of the baggage spreadsheets wrestle with:
news of the 1582 promulgation [from the Pope] had not yet reached the developers of Lotus 1-2-3, so they assumed that 1900 (being a multiple of 4) was a leap year. A few years later, Microsoft released the first version of Excel with the same mistaken leap year. If you enter Feb 28, 1900 into Excel and add one, you’ll get Feb 29, 1900 — a day that never happened, but is necessary to maintain compatibility with Lotus 1-2-3.
The things we do for backwards compat!
What about Google Sheets? They took a different path, with its own set of trade-offs:
Google Sheets had the brilliant idea to remove February 29, 1900 by shifting the first two months of 1900 over by one, so it represents January 1, 1900...[which] causes dates before March 1, 1900 to be off-by-one when importing from Excel.
And what path are we taking at Quadratic?
Using a proper datetime system has the added bonus of letting us represent dates much farther in the past than 1900, although I’d be careful with anything before 1582.
Who knew the Pope reached into Excel’s inner workings!