Errors with date functions

[jack nt]

At column A, input the dates from 1/1/1900 to 29/2/1900
Run the following codes:

Private Sub CommandButton1_Click()
Dim i%
On Error Resume Next
For i = 2 To 61
Cells.Item(i, 2) = IsDate(Cells.Item(i, 1))
Cells.Item(i, 6) = IsDate(CStr(Cells.Item(i, 1)))
If IsDate(Cells.Item(i, 1)) Then
Cells.Item(i, 3) = Month(Cells.Item(i, 1))
Cells.Item(i, 4) = Day(Cells.Item(i, 1))
Cells.Item(i, 5) = Year(Cells.Item(i, 1))
Cells.Item(i, 7) = Month(CDate(Cells.Item(i, 1).Text))
Cells.Item(i, 8) = Day(CDate(Cells.Item(i, 1).Text))
Cells.Item(i, 9) = Year(CDate(Cells.Item(i, 1).Text))
End If
Next
End Sub
We can find some strange result:
- When calculating with Cells.Item(i, 1), it losts 1 day
- With Cells.item(i, 1).Text, it seems OK
- In both case IsDate() is TRUE with 2/29/1900 ???
We know 2/29/1900 is not a date be cause year 1900 is not a leap year.

In my opinion, the error of losing 1 day because 2/29/1900 is considered to be a leap day, so 2/29/1900 to be the last day of February (2/28/1900). It causes error series to 1/1/1990.

Wiki:
February 29, known as a leap day in the [COLOR=blue]Gregorian calendar, is a date that occurs in most years that are evenly [COLOR=blue]divisible by 4, such as 1976, 2004, and 2008. Years that are evenly divisible by 100 do not contain a leap day, with the exception of years that are evenly divisible by 400, which do contain a leap day; thus 1900 did not contain a leap day while 2000 did. Years containing a leap day are called [COLOR=blue]leap years. February 29 is the 60th day of the Gregorian calendar in such a year, with 306 days remaining until the end of that year.


P/S: I haven't got the right to attach the link. I'll do it when have the right

[jack nt]

I hope to have the wright to attach now.
http://en.wikipedia.org/wiki/February_29 (http://en.wikipedia.org/wiki/February_29)

[Bob Phillips]

VBA knows about 1900, Excel follows the bug in Lotus.

Have you looked at Cells.Item(i, 1) and Cells.Item(i, 1).Text on line 61?

[jack nt]

VBA knows about 1900, Excel follows the bug in Lotus.

Have you looked at Cells.Item(i, 1) and Cells.Item(i, 1).Text on line 61?
Yes. Although IsDate() is True, but Day(), Month(), Year() are mismatch.
Can you say more what you mean?
Fotunately 1900 was so long ago.
Thanks

[Bob Phillips]

IF you debug the code and look the code for Cells.Item(i, 1) and Cells.Item(i, 1).Text when i = 61, you will see that one says 28/02/1900, the other says 29/02/1900. This is because of the way that Excel holds and shows dates.