Which date system does VBA recognise/default to?

Question

I'm writing a vba subroutine in excel to store all date values between 2 dates in a single dimension array and then check for each value in a range on another sheet.

My problem is when the code is grabbing the date values (as an integer(or double - whichever one it is actually using)) it is storing a value 1462 greater than the date value on the sheet.

I am using the 1904 date system so I can use negative time values and i'm in Australia so dates are formatted dd/mm/yyyy.

As an example;

On the sheet: 01/01/2019 = 42004

In the code: 01/01/2019 = 43466

I understand the difference is the same as the difference between date values when using 1900 vs. 1904 date systems which leads me to believe perhaps VBA is defaulting the sheet value to be in 1900 and converting the value to 1904 again when the code is run?

This workbook is set up as follows:

Sheet1 contains dates from 01/01/2019 to 25/01/2019 in Range("A1:A25").

Sheet2 contains 01/01/2019 in cell A1 and 10/01/2019 in cell A2and a activeX commandbutton.

And the code:

Private Sub CommandButton1_Click()
Dim myStart As Long
Dim myEnd As Long

myStart = Me.Range("A1").Value
myEnd = Me.Range("A2").Value

Dim v As Variant
Dim x As Long
Dim myArr As Variant
ReDim myArr(0 To 100)
x = 0

For v = myStart To myEnd
    If v = Empty Then Exit For
    myArr(x) = v
    Debug.Print ("v = " & v)
    Debug.Print ("myArr = " & myArr(x))
    x = x + 1
Next

Dim lastrow As Long
lastrow = Me.Cells(Rows.Count, 4).End(xlUp).Row
x = 0
For Each cell In Sheet1.Range("A1:A100")
Debug.Print (CDbl(cell))
    If cell = myArr(x) Then
        Me.Cells(lastrow, 3).Value = cell
        Me.Cells(lastrow, 4).Value = Format(cell, "dd/mm/yyyy")
        lastrow = lastrow + 1
    End If
    x = x + 1
Next
End Sub

The output for the cell values in columns 3 and 4 are as follows (first 5 rows for demonstration):

       C         D             Sheet1.Range("A1:A5")[when displayed as number format] 
1    43466    01/01/2019                42004
2    43467    02/01/2019                42005
3    43468    03/01/2019                42006
4    43469    04/01/2019                42007
5    43470    05/01/2019                42008

The dates are written into the cell as a short date format not numeric value.

Using the intimidate window I've noticed ?cdbl(DateValue(now)) = 43496 (31/01/2019) which is making me wonder which values are correct for the dates using the 1904 date system? Per the values in Sheet1 it should be 42034.

Is the issue caused by a data type or function I've used, is it as questioned earlier - the sheet is converting them to 1904 when the values are assigned to Sheet2 with VBA, is it a bug with excel or something else?

I found a question with the same problem here on mrexcel however the resolution there was change Excel to use the 1900 date system or subtract 1462 from the values in the code which I'm hoping to avoid both of.

Vityata Vityata · Accepted Answer · 2019-01-31T13:44:50

In general, the date system in VBA is different than the date system in Excel. You can have these 3 date system options:

Excel date
Excel date with 1904 property
VBA date

This is the difference:

The dates are converted to numbers. E.g., every date is converted to a number, but the starting number is a bit different.

In Excel, the 1 is converted to 01.January.1900;
In VBA, the 1 is converted to 31.December.1899;
In Excel with 1904, the 1 is converted to 02.January.1904;

The 1904 system (or its lack) is set per Workbook. Thus, if you have 2 workbooks in Excel, one with 1904 and one without it, it would recognize them correspondingly. The system is set in:

File > Options > Advanced > Use 1904 Date System

In general, if there is a chance, that someone somehow changes your workbook with the wrong system, consider adding this check at the openning of the workbook:

Private Sub Workbook_Open()    
    If ThisWorkbook.Date1904 Then
        MsgBox "System is 1904, consider some action!"
    End If        
End Sub

If you are wondering which system to choose - I can recommend never using the 1904.

Story for the adding VBA to Excel and the 1904 problem

Which date system does VBA recognise/default to?

2 Answers