Excel Date Calculation Bug

Question

There appears to be a bug in the way Excel is determining leap years, and, by extensions, date numerical values.

A leap year is defined as a year that can be divided by 4 with no remainder, and, if it is a century year, can also be divided by 400 with no remainder.

To that extent, 1900 was not a leap year, 2000 was.

Enter the following two dates in Excel:

01-Feb-1900

01-Mar-1900

Subtract the first date from the second date and change the format to general.

The value is shown as 29, but should be 28.

While in Excel, switch to VBA and enter the following from the immediate window:

? datediff("d","01-Feb-1900","01-Mar-1900")

The value is correctly reported as 28.

Can someone get this reported to Microsoft? I don't know if this affects other versions, but it probably does.

Answer 1

To that extent, is there any plan to correct this is future versions?

While it may be rare when a user uses a date of 1900 or earlier, I believe accuracy is important.

Hi,

I have no idea but would be very surprised to see this corrected because of the reasons given in the article I pointed you to with the impact on all dates not just 29 Feb 1900

Answer 2

You would have to check with Microsoft as to their future plans.  But, since it would affect millions of users who have adapted, and would then have to make changes to accommodate, I doubt there are plans to change it.

So far as dates prior to 1900, those are not within the Excel specifications.

Two workarounds so you won't have to deal with 1900 containing Feb 29

• Use VBA
• Use the 1904 date system

Answer 3

To that extent, is there any plan to correct this is future versions?

While it may be rare when a user uses a date of 1900 or earlier, I believe accuracy is important.

Answer 4

Hi,

It is a bug but it's a bug that was introduced deliberately to make Excel compatible with Lotus 123. See the article at the following link.

http://support.microsoft.com/kb/214326?wa=wsignin1.0