I had no idea there would be a third installment when I first started writing posts about how dates are stored in Excel and Google Sheets. But this week, I stumbled across an old post by Joel Spolsky, the former Excel program manager, that couldn't be kept secret.

For those who need to catch up, here are the links to part I and part II on how dates are stored.

For everyone else, I highly recommend reading Joel's post in its entirety because it's that captivating. However, if it's too long, here's the pertinent excerpt:

“It’s a bug in Excel!” I exclaimed.

“Well, not really,” said Ed. “We had to do it that way because we need to be able to import Lotus 123 worksheets.”

“So, it’s a bug in Lotus 123?”

“Yeah, but probably an intentional one. Lotus had to fit in 640K. That’s not a lot of memory. If you ignore 1900, you can figure out if a given year is a leap year just by looking to see if the rightmost two bits are zero. That’s really fast and easy. The Lotus guys probably figured it didn’t matter to be wrong for those two months way in the past. It looks like the Basic guys wanted to be anal about those two months, so they moved the epoch one day back.”

“Aargh!” I said, and went off to study why there was a checkbox in the options dialog called 1904 Date System.

The Ed mentioned here is Ed Fries, the guy who spent ten years developing Excel and Word before eventually moving on to lead the team that developed the Xbox. The point is, Ed knew what he was talking about.

I, on the other hand, did not. In my second post about Excel dates, I labelled Lotus 1-2-3 as having a "critical software bug" because it incorrectly considered the year 1900 to be a leap year. At the time of writing, I'd assumed the Lotus 1-2-3 developers had done this accidentally, but Ed's rationale makes much more sense. In which case, this wasn't a bug at all! It was a feature to save memory!

Okay, maybe it's a stretch to call it a feature. But it raises an important point that I should consider the historical infrastructure used when trying to understand decisions (or "bugs") made in older code. It's the same moral as in the pot roast story:

A young woman was hosting a dinner party for her friends and served a delicious pot roast.  One of her friends enjoyed it so much that she asked for the recipe, and the young woman wrote it down for her.

Upon looking over the recipe, her friend inquired, “Why do you cut both ends off the roast before it’s prepared and put in the pan?” The young woman replied, “I don’t know. I cut the ends off because I learned this recipe from my mom, and that was the way she had always done it.”

Her friend’s question got the young woman thinking and so the next day she called her mom to ask her: “Mom, when we make the pot roast, why do we cut off and discard the ends before we set it in the pan and season it?” Her mom quickly replied, “That’s how your grandma always did it, and I learned the recipe from her.”

Now the young woman was really curious, so she called her elderly grandma and asked her the same question: “Grandma, I often make the pot roast recipe that I learned from mom and that she learned from you. Why do you cut the ends off the roast before you prepare it?”

The grandmother thought for a while, since it had been years since she made the roast herself, and then replied, “I cut them off because the roast was always bigger than the pan I had back then. I had to cut the ends off to make it fit.”

Hilarious, huh?

And with that, we can now end this third and final part of the mystery series on how Excel dates are stored. Case closed.