Softwareexcellotus-1-2-3spreadsheetsdatesmicrosoftbackward-compatibility
Excel Still Counts February 29, 1900, a Day That Never Happened, Because Lotus 1-2-3 Did, and Microsoft Says Fixing It Would Shift Almost Every Date
Microsoft's support article explains that Lotus 1-2-3 treated 1900 as a leap year, Multiplan and Excel copied it to share Lotus's serial date numbers, and correcting it now would move almost every date in existing workbooks back one day.
Every date in Excel is a number. In the default system, “dates are calculated by using January 1, 1900, as a starting point,” says Microsoft’s page on Excel’s date systems, so January 1, 1900 is day 1, and July 5, 2011 becomes “the serial number 40729.” Count forward and February 28, 1900 lands on 59. The next number, 60, belongs to a day the calendar skipped.
Lotus started it. “When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year,” Microsoft writes, adding that this “caused no harm to almost all date calculations in Lotus 1-2-3.”
Microsoft then copied the mistake into its own spreadsheets. Treating 1900 as a leap year let Multiplan and Excel “use the same serial date system used by Lotus 1-2-3,” the article says, and “made it easier for users to move worksheets from one program to the other.” Joel Spolsky got the story from inside the Excel team. He started there as a program manager in June 1991 and was writing the spec for Excel Basic when he noticed the extra day. In his account of his first review with Bill Gates, a colleague explains why Lotus cut the corner: “Lotus had to fit in 640K.” Skip 1900’s exception and any year divisible by four is a leap year, a check a program can make by looking at two bits. “The Lotus guys probably figured it didn’t matter to be wrong for those two months way in the past,” Spolsky writes.
The Basic team didn’t want the wrong day, so it started its count a day earlier, on December 31, 1899, which brings it back in line with Excel’s numbers from March 1, 1900 on. When Gates asked whether anyone had really worked through all those date and time functions, Spolsky’s answer was “Yes,” “except for January and February, 1900.”
There’s a second date system hiding in the options, too. Excel also offers a 1904 system, which Microsoft calls “the default date system in earlier versions of Excel for Mac,” and it starts four years later. “The difference between the two date systems is 1,462 days,” Microsoft notes, so a date copied between workbooks set to different systems arrives about four years off.
Microsoft has kept a Lotus 1-2-3 shortcut alive for four decades, long after Lotus stopped mattering. Whatever file format replaces today’s spreadsheets will face the same choice the day it has to import an Excel file: carry day 60 forward, or break every date that came with it.