Last week, I complained that the Google Sheets epoch (December 30, 1899) made no sense. However, after exploring some more, I finally have an explanation for it!

First things first, what's an epoch? Epochs are the arbitrary start dates that computer systems use to keep track of time. These systems track time by counting the number of seconds (or days or nanoseconds or any other unit of time) that have elapsed since the chosen epoch. For example, in Unix time, the epoch is January 1, 1970 00:00:00 UTC, so the time January 1, 1970 00:00:01 UTC is represented by the integer 1. But why start from 1970? Well, I explain that in my previous post.

Of course, developers are free to choose any date they want to be the epoch for the software that they write. While researching the epoch for Google Sheets, I stumbled upon Wikipedia's fantastic page on notable software epochs, and it explained everything! It turns out that many systems prefer to use January 0th as their epoch, which in implementation often translates to December 31st of the previous year. In the case of Google Sheets, that meant that the desired epoch of January 0, 1900 became December 31, 1899.

But the Google Sheets epoch is December 30th, not December 31st. Turns out, that was done for backwards compatibility. Google Sheets is intended to support spreadsheets made using other programs, like Microsoft Excel or the even older Lotus-1-2-3. And Lotus-1-2-3, though now discontinued, had a critical software bug in how it calculated leap years. In particular, it considered the year 1900 to be a leap year, but it isn't! So, instead of correcting the formulas and breaking imported spreadsheets built upon those incorrect formulas, the epoch was changed by one day to December 30th to account for the extra but incorrect February 29th added to 1900. Ta-da! A perfectly reasonable explanation for the seemingly random epoch.

And yes, for those of you wondering, the rule for leap years is unnecessarily complex. A year is a leap year if it is divisible by 4 but not by 100, unless it's also divisible by 400. It's easier to explain with code:

def is_leap_year(year):
	if year % 4 != 0:
        return False
    elif year % 100 != 0:
    	return True
    elif year % 400 != 0:
    	return False
    else:
        return True