CODEWITHSUNDEEP
exceldate-comparisonvba
Shari W
Shari W

Reputation: 537

VBA Excel - Equal dates do not evaluate as equal

MS Excel Professional Plus v14 on Win7.

I am having trouble comparing equality for date/times.

Two dates that appear to be equal, 12/16/2013 12:19:33 pm are both dimensioned as dates. One is in a date array, the other is a date variable. arrPP is ReDim'ed later. When I do DateDiff("s",date1,date2) it yields 0.

Dim arrPP() As Date          ' During runtime shows type is Date(1 to 2, 1 to 1)
Dim dNextStartTime as Date

'...code removed ...

    If arrPP(iPP_START, lPP_index) <= dNextStartTime Then
        GoTo PP_OUT
    End If

Even though they are equal, the above evaluates to false and the wrong path is taken. This was hard to track down and causes unexpected/wrong results.

Is there an offical "gotcha" regarding date equality? Are there hidden milliseconds that need to be compared, or a way to limit the comparison down to the seconds level?

I have tried several other alternatives including placing CDate in front of the array element.

FAIL:

    If Not(arrPP(iPP_START, lPP_index) > dNextStartTime) Then
        GoTo PP_OUT
    End If

PASS: (But who would think to do this?)

    If arrPP(iPP_START, lPP_index) <= dNextStartTime Or _
       DateDiff("s",arrPP(iPP_START,lPP_index),dNextStartTime) = 0 Then
        GoTo PP_OUT
    End If

Upvotes: 1

Views: 6236

Answers (3)

Andrei Miclea
Andrei Miclea

Reputation: 1

Most likely you don't need an answer after so many years but if anybody will join this question maybe it will be useful.

Function DateEqual(date1 As Date, date2 As Date) As Boolean
    'use should never compare as equal dates or any double values but if you really need to do it carefully use this function.
    'converting to integers to test if they are equal. if you need to test time values recorded with less then 1second DO NOT USE THIS FUNCTION.
    Dim day1, day2 As Date
    Dim time1, time2 As Date
    day1 = Int(date1)
    day2 = Int(date2)
    If day1 = day2 Then
        If Hour(date1) = Hour(date2) And Minute(date1) = Minute(date2) And Second(date1) = Second(date2) Then
            DateEqual = True: Exit Function
        Else
            DateEqual = False: Exit Function
        End If
    Else
        DateEqual = False: Exit Function
    End If
End Function

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53146

This is most likely due to floating point precission issues. Dates are stored as double precission floats, where the integer part is date and fractional part is time.

To test if arrPP(iPP_START,lPP_index) is before dNextStartTime it's probably best to use

If DateDiff("s",dNextStartTime,arrPP(iPP_START,lPP_index)) <= 0 Then

Note that DateDiff returns possitive when the first date parameter is earlier than the second.

To demonstrate how two apparently equal dates may not be equal, try running this

Sub demo()
    Dim d1 As Date, d2 As Date

    d1 = #12/17/1986 12:19:33 PM#

    d2 = #12/17/1986#
    d2 = d2 + 12# / 24#             ' Add 12 hour
    d2 = d2 + 19# / 60# / 24#       ' Add 19 minutes
    d2 = d2 + 33# / 60# / 60# / 24# ' Add 33 seconds

    Debug.Print d1; d2
    Debug.Print d1 = d2
    Debug.Print d1 - d2
End Sub

Immediate window output

17/12/1986 12:19:33 p.m. 17/12/1986 12:19:33 p.m.
False
3.63797880709171E-12

Upvotes: 4

Siddharth Rout
Siddharth Rout

Reputation: 149335

VBA Excel - Equal dates do not evaluate as equal

It works for me.

I guess it boils down to how are you storing the date in the date variable or the date array. How are you populating the dates?

Here is the test that I did. Let me know if I have misunderstood your query.

Sub Sample()
    Dim dt As Date
    Dim MyAr(1, 1) As Date

    dt = #12/16/2013 12:19:33 PM#
    MyAr(1, 1) = #12/16/2013 12:19:33 PM#

    If (MyAr(1, 1) > dt) Then
        MsgBox "MyAr is greater"
    ElseIf (MyAr(1, 1) < dt) Then
        MsgBox "MyAr is lesser"
    Else
        MsgBox "They are equal" '<~~ This is what I get
        Debug.Print DateDiff("s", MyAr(1, 1), dt)
    End If
End Sub

Upvotes: 1

Related Questions