Problem with the 1/1/1900 in VBA

[Harry Covair]

Hi the forum,

I would submit you an Excel problem or bug.

In an Excel worksheet, cell B2 contains 1/1/1900, B3 = 2/1/1900 and so on.

When I apply some date functions or convert it in number, the date is well 1/1/1900.

But when, in VBA, I use the value of the cell it appears to be the 31/12/1899 which causes problem in instructions such as (x = 2)

“While Month(Cells(x, 2)) = Month(Cells(x + 1, 2))” for detecting the change of months. It gives "12 = 1" ans thus, in VBA, cell B2 has not the same month as B3.

Do some members have ever been confronted with this problem? Any solution to solve it?

Thanks in advance for the answers.

Harry

 

[NARAYANK991]

Hi Harry ,


I tried out what you posted , and the problem exists from the date 1/1/1900 till 2/28/1900 ; thereafter , everything is OK.


The only solution must be to check the date , and if it is less than 2/28/1900 , then add 1 to the value that you get in VBA.


Narayan


P.S. For some background information on this , check this :


http://www.exceluser.com/explore/earlydates.htm

 

[Harry Covair]

Hi Narayan,

Thanks for your answer.

The problem occurs only for the 1 januari 1900.

Following your advice I change my code in

If Cells(x, 2) = #12/31/1899# Then

mydate = #1/1/1900#

Else

mydate = Cells(x, 2).Value

End If

and the code works as desired

Once again thanks

Harry

PS very interesting exceluser link.