VBA dates versus Excel dates

[Morten Hvidberg-Knudsen]

There is a strange in the inconsistency between VBA dates and Excel dates.

If you execute the following VBA code:

Sub test()
Dim d As Date, dd As Date, dr As Date, r As Range, i As Integer
d = " 14 aug 2008 23:00"
dd = "00:05"
Set r = Range("A1")
For i = 1 To 16
d = d + dd
r.Offset(i) = d 'store it in a cell
dr = r.Offset(i) 'read the value back
Debug.Print d, dr
Next
End Sub

the result is:

14/08/2008 23:05:00 14/08/2008 23:05:00
14/08/2008 23:10:00 14/08/2008 23:10:00
14/08/2008 23:15:00 14/08/2008 23:15:00
14/08/2008 23:20:00 14/08/2008 23:20:00
14/08/2008 23:25:00 14/08/2008 23:25:00
14/08/2008 23:30:00 14/08/2008 23:30:00
14/08/2008 23:35:00 14/08/2008 23:35:00
14/08/2008 23:40:00 14/08/2008 23:40:00
14/08/2008 23:45:00 14/08/2008 23:45:00
14/08/2008 23:50:00 14/08/2008 23:50:00
14/08/2008 23:55:00 14/08/2008 23:55:00
15/08/2008 14/08/2008
15/08/2008 00:05:00 15/08/2008 00:05:00
15/08/2008 00:10:00 15/08/2008 00:10:00
15/08/2008 00:15:00 15/08/2008 00:15:00
15/08/2008 00:20:00 15/08/2008 00:20:00

It is seen that a incompatibility occurs "at midnight":
When you store the VBA date "15/08/2008 00:00" in a cell in Excel, then it
is interpreted as "14/08/2008 00:00". And when the cell is read back into a
VBA date variable, then it is interpreted by VBA as "14/08/2008 00:00".

I reported the error in Excel2003, but aparently it has survived into
Excel2007.

Is'nt it time to remove this very confusing error (which, depending on the
circumstances, can be very serious) ?

Regards

Morten



----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...0776281&dg=microsoft.public.excel.programming

 

[Jon Peltier]

Troublesome. I reproduced it in 2003 and 2007. Then I tried a couple things.

1. I used the .Value property of the range objects you were writing to and
reading from, rather than rely on the default (which should be .Value, but
whatever). I also put the dates within hashes rather than double quotes. The
problem persisted.

2. I did my calculations with doubles. The correct values were saved in the
worksheet.

3. I used the .Value2 property when writing the dates to the worksheet. The
problem went away in 2003 and in 2007.

I'll submit the problem as a bug, but in the meantime use the .Value2
property of the range, as in my example below.


Sub test2()
Dim dt1 As Date, dt0 As Date, dt2 As Date, dt3 As Date
Dim r As Range, i As Integer
Dim db0 As Double, db1 As Double, db2 As Double
dt1 = #8/14/2008 11:00:00 PM#
dt0 = #12:05:00 AM#
db1 = CDbl(dt1)
db0 = CDbl(dt0)
Set r = Range("A1")
For i = 1 To 16
dt1 = dt1 + dt0
db1 = db1 + db0
r.Offset(i).Value = dt1 'store it in a cell
r.Offset(i, 1).Value = db1
r.Offset(i, 2).Value2 = dt1
dt2 = r.Offset(i).Value 'read the value back
db2 = r.Offset(i, 1).Value
dt3 = r.Offset(i, 1).Value
Debug.Print dt1, dt2, dt3, CDate(db1), CDate(db2)
Next
End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

 

[Morten Hvidberg-Knudsen]

Thanks

Never heard of the ".value2" property before, but aparently now is the time
to study it.

Morten

 

[Jon Peltier]

I've hardly ever used it, but I know it can improve precision of data
transferred between worksheet and VBA.

- Jon

 

[Barb Reinhardt]

Hi Jon,

I seem to remember reading that .value2 was the best option for currency and
dates. I don't remember why.

Barb Reinhardt

 

[Dave Peterson]

It's in VBA's help???

<vbg>

Hi Jon,

I seem to remember reading that .value2 was the best option for currency and
dates. I don't remember why.

Barb Reinhardt

 

[Jon Peltier]

No, it's probably in one of the Bullen & Bovey books.

- Jon

 

[Jim Cone]

Six to seven years back, Chip Pearson and Jim Rech provided some insight...
http://tinyurl.com/6hjn3t