Dates in arrays

[tiredofit]

Something doesn't seem to be quite right with my dates in arrays.

Put a date of 02/01/1900 in cell A1, put a date of 03/01/1900 in cell B1.

Format both to be dates, (choose the type to be 14/03/2001).

If I step into my code and look at the locals window, it says:

MyArray(1,1) has a value of #01/01/1900#
MyArray(1,2) has a value of #02/01/1900#

Option Explicit

Sub Test()

Dim MyArray() As Variant

MyArray = Cells(1, 1).CurrentRegion.Value

End Sub

Why is it showing the dates out by a day?

 

[RoryA]

Excel's date formulas allow for 29/2/1900 (because Lotus did) even though it didn't exist. VBA does not do this, which means that date values in VBA pre 1st March 1900 are one day out compared to their worksheet equivalents.

 

[tiredofit]

Thanks

 

[Mike LH]

Hi,

It's a feature in Excel. Excel was created with a deliberate bug where 1900 was made a leap year; it isn't, so all dates prior to 1 Mar 1900 are 1 day out. This was done because Excel was trying to compete with Lotus 123 as a spreadsheet package and Lotus 123 had a genuine bug where it thought 1900 was a leap year. Put dates from 1 Mar 1900 and it will work fine.

have a look at this link, it's for an older version of Excel but still valid.


https://support.microsoft.com/en-us/kb/214326

 

[sreevignesh]

Thats Cool mike