February 29, 1900 bug in Excel 2011 for Mac

[ljtjr]

Excel 2011 for Mac gives a date for February 29, 1900. But there is no such date because 1900 was not a leap year (not an integer multiple of 400). The WEEKDAY() function says 2/29/1900 was a Wednesday whereas it was 2/28/1900 that was a Wednesday. Further checking shows that all WEEKDAY() functions for January and February of 1900 are correspondingly off by one day, e.g. 1/1/1900 was actually a Monday, not a Sunday as Excel says. That one-day shift allows 3/1/1900 to be correctly designated as a Thursday, as are all subsequent dates. However, the insertion of 2/29/1900 throws off by one the date-sequence numbers used in calculations for all dates after 2/28/1900. The problem is a mistake rather than a misunderstanding because other centennial years that are not leap years are handled correctly. I conjecture that the problem arose when the 1900 date system for Macs was added to the 1904 system to achieve compatibility with Excel for Windows. But for true compatibility, wouldn't the Windows version need to harbor the same mistake?

I'm wondering if this bug was ever fixed in Excel 2016 for Mac. Is it present only in Excel 2011 for Mac or is it present also in any of the Windows versions as well? I don't have access to Windows machines. It's hard to believe that for an app as widely used as Excel that this is not a known flaw, although I failed to find mention of it in this forum (but I'm new here and not facile with searching). I've kept up with upgrades for Excel 2011 and the bug persists, so I suspect Microsoft has chosen to ignore it. That would be a sad departure from the care that seems to have been taken with Excel to insure correctness otherwise.

[Pecoflyer]

Hi
perhaps this will help

https://support.microsoft.com/en-us/...ystem-in-excel

[ArviLaanemets]

This is an old and known issue. I'm not sure, it started with MS Office or it was adopted from some it's predecessor, but from start on the leap year in 1900 was incorrect. And it was knowingly not corrected afterwards, to keep continuity. So when you need to operate dates before 01.04.1900, you have take it into account yourself.

[ljtjr]

Perpetuating a flaw for the sake of "continuity" strikes me as a weak rationalization. My impression form your response is that the Windows versions suffer from the same disease. Is that correct?

[ljtjr]

The observed misbehavior is not related to the differences between the 1900 and 1904 date systems. It's just the 1900 system that's flawed. But thanks for responding.

[xor]

https://www.joelonsoftware.com/2006/...-billg-review/

[ljtjr]

Joel, thank you so much for directing me to your narrative, "My First BillG Review." It seems plausible that Lotus may have been the historical origin of the flaw that troubled me. You may think me "anal," but cheating on the leap-year algorithm to save memory seems to me to be irresponsible. I've been retired for more than 20 years so I'm aware that I'm out of touch with current practices. Back in the mid 1960's I was into biomedical computing which included in the early 1970s some systems for critical care of patients. Our computers (the LINC and its derivatives) initially had 1K of 12-bit magnetic-core memory. When they were expanded to 2K we wondered what we'd do with all that memory. Yes, memory was at a premium, but as lives depended on it, the highest priority was on exact and reliable correctness of the assembly-language programs without compromise. Hence my mindset.

You report the suggestion that an intentional bug is not really a bug. For me, an intentional bug is the worst kind, an unconscionable bug. I had imagined that what seems to be perpetuated sloppiness would have been fixed by now. It was Laurence J. Peter who observed, "Computers magnify the confusion of their users." Maybe that should be modified to ...magnify and perpetuate... So ends my rant. As a result of your response I now regard my questions as "solved." Thank you.

[Pecoflyer]

Originally Posted by ljtjr

As a result of your response I now regard my questions as "solved." Thank you.

Then please mark it as such

[xor]

Windows versions (as from Excel 2.0 where the 1900 date system was introduced) suffer from the same intentional error (I would call it).

https://support.microsoft.com/en-gb/...is-a-leap-year

[ljtjr]

Yes, that's as I surmised. See my response to macropod.

[macropod]

As the link provided by xor explains, the issue arose because, when Excel was introduced, Lotus 123 was the dominant spreadsheet program and Microsoft was more or less obliged to replicate the bug so as to be able to work with Lotus worksheets and vice-versa. Having done that, later versions of Excel likewise have had to maintain the bug for backwards compatibility. Correcting the bug could cause data loss and/or calculation errors in a wide range of workbooks with severe financial implications. Furthermore, if Microsoft were to correct the 1900 leap-year 'bug' in Excel, that would immediately make it incompatible with every other worksheet program out there, all of which have the same bug and for essentially the same reasons.

You might think Microsoft's reasons are weak - but just look at the furore that's been created by Apple doing something as innocuous as reducing processor performance on devices using old batteries... Somehow, I doubt Microsoft wants to be bankrupted for having the temerity to fix a long-documented, historical, bug that wasn't of its own design.

[ljtjr]

OK, you guys win. The message I take from this is that for practical reasons, carelessness and/or ignorance can have long term consequences. Accordingly, it's important to get it right the first time. This aged purist will now slink away. OK?