dailynewsnblog
Nestled away amongst a pile of recently updated pages on Microsoft’s Learn site, which contains product documentation and technical resources, was a curiously titled article: “Excel incorrectly assumes that the year 1900 is a leap year.” I thought that can’t be right, surely everything would have fallen over long ago, but it turns out that this problem has long been known about among Excel-fanciers and Microsoft is never going to fix it: because then everything would collapse.
“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,” reads the Microsoft explanation. “This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.”
Article continues below
Without delving too deep into the history of spreadsheet software, suffice to say that Microsoft’s office software would overtake Lotus’ products in the early 90s, and go on to dominate the market (though Lotus products only stopped being sold in 2013).
But that leap year inheritance remains and, while Microsoft says it “is technically possible to correct this behavior […] the disadvantages of doing so outweigh the advantages.” It goes on to list some of the issues that would arise:
- Almost all dates in current Microsoft Excel worksheets and other documents would be decreased by one day. Correcting this shift would take considerable time and effort, especially in formulas that use dates.
- Some functions, such as the WEEKDAY function, would return different values; this might cause formulas in worksheets to work incorrectly.
- Correcting this behavior would break serial date compatibility between Microsoft Excel and other programs that use dates.
Yeah… you can kind of see why that might be bad. Excel remains the industry standard in business software, still dominates the market, and is estimated to have between 750 million and a billion worldwide users. If Microsoft fixed this issue then the knock-on consequences, beyond the immediately obvious, are impossible to predict.
As Microsoft itself notes, leaving the leap year behaviour as-is leads to only one problem: “The WEEKDAY function returns incorrect values for dates before March 1, 1900.” It adds with magnificent understatement that “because most users do not use dates before March 1, 1900, this problem is rare.”
Leap year bugs or the “leap year problem” are a software genre unto themselves, though this is a particularly beautiful example. Whatever its origin, this bug became a feature for Excel, and these days is so entrenched that even the likes of Open Office XML have promoted it to a requirement.
