Why does Excel incorrectly treat 1900 as a leap year?
Microsoft's spreadsheet software, Excel, treats the year 1900 as a leap year, even though it wasn't actually one. Microsoft explains that this is for compatibility with older spreadsheet software, and Joel Spolsky, who started working on the Excel team in 1991, also documented the situation in his blog in 2006.
Excel incorrectly assumes that the year 1900 is a leap year - Microsoft 365 Apps | Microsoft Learn
My First BillG Review – Joel on Software
https://www.joelonsoftware.com/2006/06/16/my-first-billg-review/
Leap years generally occur every four years, but years divisible by 100 are not leap years. However, years divisible by 400 are an exception and are leap years. 1900 is a year divisible by 100 but not by 400, so it is not a leap year. For example, 2100 is similar to 1900 in that it is divisible by 100 but not by 400, but it will not be treated as a leap year according to the rules.
Microsoft has admitted that Excel incorrectly treats 1900 as a leap year. This is because the widely used spreadsheet software
Spreadsheet software sometimes manages dates as sequential numbers. In Excel, January 1, 1900 is treated as '1,' and the number increases by 1 for each subsequent day. However, this date counting method includes February 29, 1900, a date that does not actually exist. Therefore, for dates before March 1, 1900, the WEEKDAY function , which calculates the day of the week from a date, will produce an incorrect value that is off by one day.
When I actually entered dates around the leap day in 1900 into Excel, I found that February 29th, a date that didn't actually exist in 1900, was present and treated as a 'Wednesday.' In reality, February 28th, 1900 was a Wednesday, so the days of the week for each date from January 1st, 1900 to February 28th, 1900, are shifted by one day.
Microsoft explains that the reason they haven't fixed it yet is that 'fixing the issue of 1900 being treated as a leap year would shift almost all dates in current Excel worksheets and other documents by one day, and would also have a widespread impact on formulas that use dates.'
While making the correction would change the results of the WEEKDAY function and break compatibility with other programs that use dates, the problems caused by treating 1900 as a leap year are limited to the period between January 1, 1900 and March 1, 1900, so they rarely surface. For this reason, Microsoft has explained that 'not making the correction has less impact,' and that treating 1900 as a leap year is an exception for compatibility purposes, and that leap years other than 1900 will be handled correctly.
On June 16, 2006, Spolsky documented the situation in a blog post. After starting work with the Excel team on June 17, 1991, Spolsky noticed something odd while investigating the difference in the date base dates used by Excel and Visual Basic . Excel counts dates starting from January 1, 1900, while Visual Basic uses December 31, 1899 as its base date. Despite this, the date value for June 16, 2006 was the same in both, which he found puzzling.
When Spolsky asked an Excel developer who was familiar with the situation at the time, he was told that 'that was the only way to make it possible to import Lotus 1-2-3 worksheets.' Through this exchange, Spolsky wrote that he 'learned that instead of March 1, 1900 following February 28, 1900, there was a non-existent February 29, 1900 in between.'
Furthermore, Spolsky writes that because Lotus 1-2-3 needed to fit into 640KB of memory, it's possible that 1900 was treated as a leap year to simplify processing. However, this is a mix of explanations he heard from Excel developers at the time and Spolsky's own speculation, and Microsoft's official explanation is simply that they prioritized compatibility with Lotus 1-2-3.
Related Posts:
in Software, Posted by log1b_ok