What is the difference between Excel Dates and VBA Dates ?

Dear all,

In this piece of Excel VBA code:

Sub a()
Dim Datim As Date
Dim Outr As Range

    Set Outr = Range("b2")
    Datim = "17/03/2006 12:00"
    Do
        Outr.Value = Datim
retry:
        If Abs(Datim - Outr) > 0.0000001 Then
            Debug.Assert False
            Outr = Datim + 1
            GoTo retry
        End If
        Datim = Datim + CDate("00:05:00")
        Set Outr = Outr.Offset(1)
    Loop Until Datim > "1 apr 2006"

End Sub

the code marked red shows that if you store the VBA date "18/03/2006 00:00" in a spreadsheet cell the contents of this cell becomes "17/03/2006 00:00" and when it is read it back into VBA then you get the value "17/03/2006 00:00"

However, if you store "18/03/2006 00:00" +1 in the spreadsheet cell and read it back then you get "18/03/2006 00:00".

This behaviour only occurs when the VBA date is "integer", i.e. when the hour and minute part of the date is "00:00". Otherwise dates transfer as expected.

Are there any explanations for that (or is it only my Excel2003 which behaves like that )

Regards

Morten

Microsoft ISV Community Center Forums1

Hi Morten

If it's any comfort I get the same issue with Excel 2003 on my PC. Interestingly( ) if you replace the line after the Do with

Outr.Value = Cdate(Cstr(Datim))

then it works as you would have hoped.

PeterMo.

Hi PeterMo

Thanks for your effort: nice to know that it I havnt completely lost my mind.

But it surely puzzels me that it can behave like that. Basically I thought thay the "date" data type, both in VBA and in Excel, was basically a "double" (internally and with respect to arithmetic) which by default is formatted as a date when presented to the "user".

Morten