Email Address Password
Remember Me

Or Create a (Free) Account.
2004JanFebMarAprMayJunJul Aug Sep Oct Nov Dec
2005 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2006 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Oct Oct
2007JanFebMarAprMayJunJulAugSepOctNovDec2007
2008JanFebMarAprMayJunJulAugSepOctNovDec2008
2009JanFebMarAprMayJunJulAugSepOctNovDec2009
2010JanFebMarAprMayJunJulAugSepOctNovDec2010
2011JanFebMarAprMayJunJulAugSepOctNovDec2011
2012JanFebMarAprMayJunJulAugSepOctNovDec2012
2013JanFebMarAprMayJunJulAugSepOctNovDec2013
2014JanFebMarAprMayJunJulAugSepOctNovDec2014

Thu 18th Jan 15:49 2007: 1900 and all that

I missed this discussion last summer; it seems that Excel 2000 incorrectly assumes that the year 1900 is a leap year - or, more accurately, MS policy has been, since the birth of Excel, and now remains indefinitely for backwards compatibility, that all versions of Excel deliberately assume that 1900 was a leap year.

The rules: Every four years, it's a leap year. If it's divisible by 100, then unless it's also divisble by 400, it's not a leap year.

So...

YearLeap Year?
1900No
2000Yes
2100No
2200No
2300No
2400Yes
2500No
2600No
2700No
2800Yes


Excel had to be compatible with Lotus 1-2-3, which needed to fit into 640Kb, so it just "cheated" and came up with a solution that worked from 1st March 1900 - 28th Feb 2400. It didn't deal with dates before 1900, and neither does Excel. (Really. The Epoch is 1/1/1900, and there's no going back before then.)

So 1900 looks like this:
DateFrom EpochWEEKDAY()Notes
27/02/190058MonNooo! The 26th was Mon
28/02/190059TueNooo! The 27th was Tue
29/02/190060WedNooo! The 28th was Wed
01/03/190061ThuYes! Hurrah!

2000, 2100, 2200, 2300, all the way to 9999, all work properly. Here's 2300 as an example (not a leap year, in exactly the same way that 1900 is not a leap year):
DateFrom EpochWEEKDAY()Notes
27/02/2300146156Tue
28/02/2300146157Wed
29/02/230029/02/2300#VALUERight! There is no 29th. Excel can't convert this to an integer difference from the Epoch, so it just treats it as a string.
01/03/2300146158Thu


Note that this means that there is explicit code in Excel, today, which understands the correct rules for leap years, and which then says (effectively):

/* let's see if this is a leap year? */
bool is_leap_year;

/* get_leap_year_status knows **ALL** the rules, and gets it right, according to the Gregorian Calendar */
is_leap_year=get_leap_year_status(year)

/* But we'll remain compatible with Lotus 1-2-3... */
if (year==1900) { is_leap_year=TRUE; }


Why does this matter? Well, some people will need to use dates going back to 1900 (it's only 107 years ago; there are many people still alive over 107 years old). But also, MS influenced the Oasis OOXML standard, to get them to say in their specification:

For legacy reasons, an implementation using the 1900 date base system shall treat 1900 as though it was a leap year. [Note: That is, serial value 59 corresponds to February 28, and serial value 61 corresponds to March 1, the next day, allowing the (nonexistent) date February 29 to have the serial value 60. end note] A consequence of this is that for dates between January 1 and February 28, WEEKDAY shall return a value for the day immediately prior to the correct day, so that the (nonexistent) date February 29 has a day-of-the-week that immediately follows that of February 28, and immediately precedes that of March 1.

So everybody else, whether or not they need interoperability with Excel, must now work around Microsoft's deliberate bug, if they want to work with Office Open XML. Microsoft's bug is now officially a standard. The Rob Weir site linked above includes a comment that sums it up nicely:

Q: How many Microsoft employees does it take to change a light bulb?
A: None. Darkness is the new industry standard.


Comments for '1900 and all that'

You could post a comment if you were logged in.

You are logged in as 0

create an account

Steve's urandom blog
Share on Twitter Share on Facebook Share on LinkedIn Share on Identi.ca Share on StumbleUpon
My Shell Scripting Book:
    Shell Scripting, Expert Recipes for Linux, Bash and more
is available online and from all good booksellers:


DefectiveByDesign.org