The following Excel VBA code compares the effect of accumulating hours (1/24 of a day) in a Variant array with Date members, and in a Date array. I have columns C, E, G formatted as Date and D, F, H as Number with a lot of d.p.s. In columns A and B I replicated the calculation using worksheet functions. You probably won't believe this until you try it, but the last output from the Variant array of Date is 1 day early. The numeric value, which is reported as 39814.9999999999 by VBA, is converted to 39814 in Excel.
Edit: when I say columns C, E, G in Date format, I mean Custom "dd/mm/yyyy hh:mm:ss.00".
Has anybody seen this before? But more importantly, how do I know what I can trust when using Variants? I have tens of thousands of lines to maintain and Variant arrays are used everywhere that we need to do intensive processing on the Excel data.
Sub FillDatesBug()
Dim dtHours() As Date
Dim vHours As Variant
Dim vHours2 As Variant
Dim dtHour As Date
Dim dHour As Double
Dim i As Long
ReDim dtHours(1 To 25, 1 To 1)
ReDim vHours(1 To 25, 1 To 1)
ReDim vHours2(1 To 25, 1 To 1)
dtHour = CDate(1 / 24)
dHour = 1 / 24
dtHours(1, 1) = CDate(39814)
vHours(1, 1) = CDate(39814)
vHours2(1, 1) = 39814#
For i = 2 To 25
dtHours(i, 1) = dtHours(i - 1, 1) + dtHour
vHours(i, 1) = vHours(i - 1, 1) + dtHour
vHours2(i, 1) = vHours2(i - 1, 1) + dHour
Next i
Range("C2:C26").Value = dtHours
Range("D2:D26").Value = dtHours
Range("E2:E26").Value = vHours
Range("F2:F26").Value = vHours
Range("G2:G26").Value = vHours2
Range("H2:H26").Value = vHours2
Range("C28").Value = "dtHours(25,1) = " & dtHours(25, 1) & " or " & CDbl(dtHours(25, 1))
Range("E28").Value = "vHours(25,1) = " & vHours(25, 1) & " or " & CDbl(vHours(25, 1))
Range("G28").Value = "vHours2(25,1) = " & Format(vHours2(25, 1), "dd/mm/yyyy hh:mm:ss") & " or " & vHours2(25, 1)
End Sub
And here's a screenshot - I don't have the rep to display it, apparently.
As you can see, a Date array and a Variant array with Double members get the right answers (including the perfectly normal and understood numerical error). Certain values in a Variant array with Date members get translated into Excel wrongly.
First person to actually see what I'm saying here wins my eternal gratitude.
EDIT (better explanation). I have seen something similar in the past, but do not recall the details. In this particular case, it seems to be related, at least in part, to some of the implicit data conversions going on when using Variant data types, and writing results to the worksheet.
One fix, if you must use Variant arrays, is to round to your desired level of precision. So you would make the following changes, to round, for example to milliseconds:
For i = 2 To 25
vHours(i, 1) = Round((vHours(i - 1, 1) + dtHour) * 86400000, 0) / 86400000
dtHours(i, 1) = Round((dtHours(i - 1, 1) + dtHour) * 86400000, 0) / 86400000
Next I
EDIT Additional observation
If the variant array is written back to the sheet using the Range.Value2 property, instead of the Range.Value property, the date displayed in the worksheet cell will be correct. However, the date displayed in the formula bar will be incorrect (one day earlier than expected). This seems to be an Excel problem as a value, such as 39814.99999999999 entered directly into a cell will also demonstrate different dates in the formula bar vs the worksheet cell.
It would seem the safest, and fastest method of dealing with this (and other issues) would be to use the Value2 property when reading/writing between variant arrays and the worksheet. Interesting discussion here, and also at the link referenced by Williams in his response.
If you want the dates in the formula bar and the worksheet cell to agree, I see no other option than rounding. But that may not be necessary.