is 1900 a Leap Year?
Why is MSExcel accepting the string `2/29/1900' as a legal date? In fact it also determines '2/29/1900' as a WEDNESDAY. 1900 is not a leap year since it is divisible by 100 and not divisible by 400. January 1, 1900 is marked as Sunday by MSExcel. According to Perpetual Calendar available on the web, it is a Monday. The corresponding weekday for each date from Jan 1, 1900 to Feb. 28, 1900 is not correct. The weekdays from March 1, 1900 onwards are correct. However, the serial numbers starting from March 1, 1900 are wrong. This is the reason why when I computed for the number of days from Jan 1, 1900 I always get a result that is higher by 1 from the correct figure. -- Felix Muga II Mathematics Department Ateneo de Mania University Philippines |
is 1900 a Leap Year?
The story goes that since Lotus 123 was the dominant spreadsheet program when
Excel was being developed and since Lotus 123 thought that 1900 was a leap year, that MS had a choice--do it correctly or do it to match Lotus 123. By matching Lotus 123's mistake, it would be easier for users to migrate from 123 to Excel. And since every company wants users and wants to make that transition as easy as possible, the decision was probably very easy to make. Lex_Muga wrote: Why is MSExcel accepting the string `2/29/1900' as a legal date? In fact it also determines '2/29/1900' as a WEDNESDAY. 1900 is not a leap year since it is divisible by 100 and not divisible by 400. January 1, 1900 is marked as Sunday by MSExcel. According to Perpetual Calendar available on the web, it is a Monday. The corresponding weekday for each date from Jan 1, 1900 to Feb. 28, 1900 is not correct. The weekdays from March 1, 1900 onwards are correct. However, the serial numbers starting from March 1, 1900 are wrong. This is the reason why when I computed for the number of days from Jan 1, 1900 I always get a result that is higher by 1 from the correct figure. -- Felix Muga II Mathematics Department Ateneo de Mania University Philippines -- Dave Peterson |
is 1900 a Leap Year?
This is supposedly a Lotus 123 bug that MS copied to be compatible when
Lotus was the main spreadsheet, this means that calculation with dates prior to Mar 1 1900 will be off by one day so you need to take that in consideration -- Regards, Peo Sjoblom (No private emails please) "Lex_Muga" wrote in message ... Why is MSExcel accepting the string `2/29/1900' as a legal date? In fact it also determines '2/29/1900' as a WEDNESDAY. 1900 is not a leap year since it is divisible by 100 and not divisible by 400. January 1, 1900 is marked as Sunday by MSExcel. According to Perpetual Calendar available on the web, it is a Monday. The corresponding weekday for each date from Jan 1, 1900 to Feb. 28, 1900 is not correct. The weekdays from March 1, 1900 onwards are correct. However, the serial numbers starting from March 1, 1900 are wrong. This is the reason why when I computed for the number of days from Jan 1, 1900 I always get a result that is higher by 1 from the correct figure. -- Felix Muga II Mathematics Department Ateneo de Mania University Philippines |
is 1900 a Leap Year?
An attempt to "justify" this "feature" is listed here...
Excel 2000 incorrectly assumes that the year 1900 is a leap year http://support.microsoft.com/kb/214326/en-us :) -- Dana DeLouis Win XP & Office 2003 "Lex_Muga" wrote in message ... Why is MSExcel accepting the string `2/29/1900' as a legal date? In fact it also determines '2/29/1900' as a WEDNESDAY. 1900 is not a leap year since it is divisible by 100 and not divisible by 400. January 1, 1900 is marked as Sunday by MSExcel. According to Perpetual Calendar available on the web, it is a Monday. The corresponding weekday for each date from Jan 1, 1900 to Feb. 28, 1900 is not correct. The weekdays from March 1, 1900 onwards are correct. However, the serial numbers starting from March 1, 1900 are wrong. This is the reason why when I computed for the number of days from Jan 1, 1900 I always get a result that is higher by 1 from the correct figure. -- Felix Muga II Mathematics Department Ateneo de Mania University Philippines |
is 1900 a Leap Year?
"Dave Peterson" wrote:
since Lotus 123 thought that 1900 was a leap year, that MS had a choice--do it correctly or do it to match Lotus 123. By matching Lotus 123's mistake, it would be easier for users to migrate from 123 to Excel. And since every company wants users and wants to make that transition as easy as possible, the decision was probably very easy to make. As a developer of system software, I understand the importance of compatibility. But assuming the explanation is correct, there should be an option to correct the problem. The default could remain bug-for-bug compatibility. |
is 1900 a Leap Year?
While it's understandable that MS wanted to have compatibility with Lotus, they
could have preserved the compatibility and also fixed the bug. To allow for an easy transition, MS wanted to ensure that, for example, date serial number 31,406 was Dec 25/85, as it was in Lotus. So they programmed for this. Now the only problem is with the first 60 days of the 20th century. In Lotus, date serial number 1 is 1/1/1900 and date serial 60 is 2/29/1900, when there is no such date. All MS had to do is say date serial 1 is 12/31/1899 and 60 is 2/28/1900. Voila, problem solved for all posterity. Yes, it would have caused a problem with any spreadsheet at the time that used the first 60 days of the century. But the number of affected spreadsheets at the time would have been infinitesimal, and not worth worrying about (as they could just stay with Lotus). I still think MS should introduce this fix, and at the same time support negative numbers for dates prior to Dec 31/1899. -- Regards, Fred " wrote in message ... "Dave Peterson" wrote: since Lotus 123 thought that 1900 was a leap year, that MS had a choice--do it correctly or do it to match Lotus 123. By matching Lotus 123's mistake, it would be easier for users to migrate from 123 to Excel. And since every company wants users and wants to make that transition as easy as possible, the decision was probably very easy to make. As a developer of system software, I understand the importance of compatibility. But assuming the explanation is correct, there should be an option to correct the problem. The default could remain bug-for-bug compatibility. |
is 1900 a Leap Year?
Fred proposal could be one way of fixing the bug. I also support the introduction of negative numbers before Dec 31, 1899. -- Felix Muga II Mathematics Department Ateneo de Mania University Philippines "Fred Smith" wrote: While it's understandable that MS wanted to have compatibility with Lotus, they could have preserved the compatibility and also fixed the bug. To allow for an easy transition, MS wanted to ensure that, for example, date serial number 31,406 was Dec 25/85, as it was in Lotus. So they programmed for this. Now the only problem is with the first 60 days of the 20th century. In Lotus, date serial number 1 is 1/1/1900 and date serial 60 is 2/29/1900, when there is no such date. All MS had to do is say date serial 1 is 12/31/1899 and 60 is 2/28/1900. Voila, problem solved for all posterity. Yes, it would have caused a problem with any spreadsheet at the time that used the first 60 days of the century. But the number of affected spreadsheets at the time would have been infinitesimal, and not worth worrying about (as they could just stay with Lotus). I still think MS should introduce this fix, and at the same time support negative numbers for dates prior to Dec 31/1899. -- Regards, Fred " wrote in message ... "Dave Peterson" wrote: since Lotus 123 thought that 1900 was a leap year, that MS had a choice--do it correctly or do it to match Lotus 123. By matching Lotus 123's mistake, it would be easier for users to migrate from 123 to Excel. And since every company wants users and wants to make that transition as easy as possible, the decision was probably very easy to make. As a developer of system software, I understand the importance of compatibility. But assuming the explanation is correct, there should be an option to correct the problem. The default could remain bug-for-bug compatibility. |
is 1900 a Leap Year?
"Dave Peterson" wrote:
That KB article that Dana posted explains a few reasons why it's not as simple as a option in Tools|options. I think I fully understand all the issues. I am not in the habit of giving free consulting to Mr. Gates' boys, and I would not presume to tell them how to solve their problems without looking at the code first myself. However, I think Fred Smith probably got it right. I hope he rushes out and patents the "elusive" solution ;-) so that he can get his due from Bill. |
is 1900 a Leap Year?
And.....
Support for negative numbers as they apply to dates prior to Dec 31 1899 should also extend to negative TIME calculations *without* having to change to the 1904 date system. (which can cause other problems) I'm thinking that this is rooted so deeply in the code and has been propagated to so many other areas that it could never be fixed without a "Y2K" effort. Read: money needed to fix it! Biff "Lex_Muga" wrote in message ... Fred proposal could be one way of fixing the bug. I also support the introduction of negative numbers before Dec 31, 1899. -- Felix Muga II Mathematics Department Ateneo de Mania University Philippines "Fred Smith" wrote: While it's understandable that MS wanted to have compatibility with Lotus, they could have preserved the compatibility and also fixed the bug. To allow for an easy transition, MS wanted to ensure that, for example, date serial number 31,406 was Dec 25/85, as it was in Lotus. So they programmed for this. Now the only problem is with the first 60 days of the 20th century. In Lotus, date serial number 1 is 1/1/1900 and date serial 60 is 2/29/1900, when there is no such date. All MS had to do is say date serial 1 is 12/31/1899 and 60 is 2/28/1900. Voila, problem solved for all posterity. Yes, it would have caused a problem with any spreadsheet at the time that used the first 60 days of the century. But the number of affected spreadsheets at the time would have been infinitesimal, and not worth worrying about (as they could just stay with Lotus). I still think MS should introduce this fix, and at the same time support negative numbers for dates prior to Dec 31/1899. -- Regards, Fred " wrote in message ... "Dave Peterson" wrote: since Lotus 123 thought that 1900 was a leap year, that MS had a choice--do it correctly or do it to match Lotus 123. By matching Lotus 123's mistake, it would be easier for users to migrate from 123 to Excel. And since every company wants users and wants to make that transition as easy as possible, the decision was probably very easy to make. As a developer of system software, I understand the importance of compatibility. But assuming the explanation is correct, there should be an option to correct the problem. The default could remain bug-for-bug compatibility. |
is 1900 a Leap Year?
Why is Microsoft sacrificing accuracy even up to now?
Does it have no moral obligation to uphold what is computationally right? Considering its vast resources, there is no reason it cannot correct this error. -- Felix Muga II Mathematics Department Ateneo de Mania University Philippines "Dana DeLouis" wrote: An attempt to "justify" this "feature" is listed here... Excel 2000 incorrectly assumes that the year 1900 is a leap year http://support.microsoft.com/kb/214326/en-us :) -- Dana DeLouis Win XP & Office 2003 "Lex_Muga" wrote in message ... Why is MSExcel accepting the string `2/29/1900' as a legal date? In fact it also determines '2/29/1900' as a WEDNESDAY. 1900 is not a leap year since it is divisible by 100 and not divisible by 400. January 1, 1900 is marked as Sunday by MSExcel. According to Perpetual Calendar available on the web, it is a Monday. The corresponding weekday for each date from Jan 1, 1900 to Feb. 28, 1900 is not correct. The weekdays from March 1, 1900 onwards are correct. However, the serial numbers starting from March 1, 1900 are wrong. This is the reason why when I computed for the number of days from Jan 1, 1900 I always get a result that is higher by 1 from the correct figure. -- Felix Muga II Mathematics Department Ateneo de Mania University Philippines |
is 1900 a Leap Year?
"Lex_Muga" wrote:
Why is Microsoft sacrificing accuracy even up to now? Does it have no moral obligation to uphold what is computationally right? Disclaimer: As you read the following, please keep in mind that I was the first person in this thread to suggest that MS could/should provide a fix. Having said that, I also know that the decision is a judgment call -- meaning there is not one right answer. Without knowing the internals of Excel and all the ways in which the product might be used, I cannot say which judgment is right. Considering its vast resources, there is no reason it cannot correct this error. I doubt that the issue is cost or engineering resources. In fact, ironically I know of instances where a company expended more energy into reversing a fix in order to preserve bug-for-bug compatibility. The arguments against an option to let the user decide are sometimes based on usability, testability, interoperability and even compatibility in other respects. It depends greatly on how the product is used -- or at least on how the decision makers perceive that the product is used. I generally do not agree with such judgments. But I can appreciate the fact that they have some legitimacy. |
All times are GMT +1. The time now is 04:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com