1

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
Improve this question
1
  • VBA Excel - Equal dates do not evaluate as equal How are you populating the dates? It works for me. Commented Oct 22, 2013 at 4:40

3 Answers 3

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Hmm. It's interesting that DateDiff() comes to 0 but a = b comes out false. Maybe DateDiff() is built to handle the imprecision? It seems like that would cause people a lot of problems!
DateDiff calculates the difference to the resolution specified (seconds in this case) The example I just added shows a difference due to floating precission of ~0.3 microseconds
Bottom line, comparing floating point values for equality (no matter what they represent) is a bad idea
Guess I'll be switching just about all of my date comparisons to DateDiff(). Thanks!
+ 1 Nice example. BTW try your code with #1/1/2001 12:19:33 PM# ;)
|
1

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
Improve this answer

1 Comment

Your example works for me, too, even when I change the first condition to If (MyAr(1,1) = dt). It evaluates to true. It also works if I put the date in cell $A$1 and read it from there. My arrPP is populated by reading date-formatted cells in my worksheet. The dates in those cells look normal. dNextStartTime is initialized to "12:00" and is usually reset to a date read from the worksheet in a date-formatted cell (looks normal). dNextStartTime might be reset again from a date in a variant array, but then I use dNextStartTime = CDate(arrOther(x,y)). Equality important for scheduling app.
0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.