« Two annoying GMail chat bugs | Main | Hindu vs. Hindi: a PSA »
November 22, 2006
Date-related hijinks in Microsoft Excel and Google Spreadsheets
For years, Microsoft Excel has been the de facto standard against which all other spreadsheet programs have been judged. Because so many Microsoft customers use Excel, sometimes running their entire business off it, it has some bugs that are well known but will not be fixed so as to preserve backwards compatibility. For example, Microsoft Excel thinks that 1900 was a leap year, when according to Gregorian calendar rules, it is not (years that end in -00 must be divisible by 400 to be leap years). Preserving backwards compatibility is important in Microsoft's business model, or else people would never upgrade to newer versions of their software. In fact, so paramount is that concern that in a move that seems pretty boneheaded to be honest, this bug has been baked into an ECMA standard to ensure its propagation into perpetuity(see last link for a quote).
Google obviously does not have as big an issue with preserving backwards compatibility, because it deploys software on its own machines. Still, in matters like these, Google can't just change certain behaviors and break thousands of user documents overnight. Besides, if Google wants to inflict death by a thousand cuts on Microsoft's Office empire, it might also need to preserve the same quirks to make conversion from Excel to its spreadsheets easier. So, I fired up Google Spreadsheets (seriously guys, awfully boring name choice) to see what it thought of 1900. Screenshots from both Microsoft Excel and Google Spreadsheets are below:
 |  |
| | |
The images above both show date equivalents of ordinary numbers in both spreadsheet programs. Both spreadsheet applications convert ordinary numbers into dates by counting those many days forward from a starting point. For Microsoft Excel, that point is Jan 1, 1900. So, 2 would be converted to Jan 2, 1900, the second day of 1900 and so on. February 28th is the 59th day of any year, so 59 should correspond to Feb 28, 1900 in Microsoft Excel. The next day, the 60th day of 1900, should be Mar 1, 1900 but because of the bug in Microsoft Excel the 60th day is shown as Feb 29, 1900, a date that never existed.
Now Google is trying to do the 'right thing' and be Excel-compatible at the same time. Notice how in the right image the date after 2/28/1900 is not in February anymore? This is how Google does the 'right thing', by not considering 1900 a leap year. To maintain Excel compatibility as much as possible, it chooses its starting point as Dec 31, 1899 — one day before Excel's starting point. This way, all the dates in January and February 1900 are assigned a number one higher than what they'd be assigned in Microsoft Excel. Starting Mar 1, 1900 however, all dates will have the same day numbers assigned to them.
Obviously, this is somewhat of a tradeoff. All Excel spreadsheets that contain dates in January and February 1900 will not carry over properly into Google Spreadsheets. In real life though, unless someone is using Microsoft Excel to keep track of interest accruals dating back to 1900, when a state-of-the-art computer was a human being, the total number of such spreadsheets is close to zero, if not zero altogether. This is a fine tradeoff to make, and a smart one too — it retains geek cred but keeps Google Spreadsheets compatible (in practice anyway) with the ones from Microsoft Excel. Kudos to Google for this!
Posted by Vishy at November 22, 2006 08:06 PM