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/comm...el.programming