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

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

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

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

"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.

That KB article that Dana posted explains a few reasons why it's not as simple
as a option in Tools|options.

wrote:

"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.

--

Dave Peterson

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.

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.

"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.

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.

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

"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.