Function comparing date values

Question

I have a VBA function where I am checking two dates StartDate and ReferenceDate against each other. Is the StartDate is less than the ReferenceDate then do something else do something else.

The code is as follows:

Function InPost(ReferenceDate As Variant, StartDate As Variant, EndDate As Variant, Status As Variant)
    If Left(Status, 7) = "Current" Then
        If IsDate(StartDate) = True And IsDate(StartDate) <= IsDate(ReferenceDate) Then
            InPost = "YES"
        Else
            InPost = "NO"

        End If
    End If
End Function

I however am having an issue where say the ReferenceDate = "31/3/2018" and the StartDate = "04/02/2019" The code should return an InPost value equal to "NO" as the StartDate > ReferenceDate but instead the code returns InPost equal to "YES"

Where have I gone wrong?

```isdate()``` returns a boolean so ```IsDate(StartDate) <= IsDate(ReferenceDate)``` always will equate to ```true``` — Warcupine, Aug 27 '19 at 13:43

PeterT · Answer 1 · 2019-08-27T14:26:09.370

You've got a few things to clean up here.

Your function is not specifying a return type. This is recommended, even if you want to return a Variant.
Declare your parameters as either ByVal or ByRef for these reasons.
Since this is a function, there are a variety of logic branches that aren't returning any value at all. You want your function to be fully "functional", so account for all possible cases.
You're checking if your input parameters (declared as Variants) are actually Dates. VBA does not have "short circuit evaluation" for an If statement. What this means is that ALL logical conditions between If and Then MUST be evaluated. So in your code above, when you check IsDate(StartDate) and then go ahead and use it in the same statement, it could fail when StartDate is not a Date type. While it's tedious, you need to remember this and break up the statement when this type of situation is possible.

EDIT: I realized I didn't answer your original question. Your date comparison is not working when your "dates" are expressed in String form. What's happening is you are comparing a string to a string, not a date to a date. The IsDate function returns True if a string looks like a date. For a proper comparison, you have to convert your String date to a Date type. Then your comparison will work. See updated code below.

Here is your example reworked to illustrate what I mean:

Option Explicit

Sub test()
    Debug.Print InPost(#3/31/2018#, #4/2/2019#, vbNullString, "Current")
    Debug.Print InPost("31/3/2018", "04/02/2019", vbNullString, "Current")
End Sub

Function InPost(ByVal ReferenceDate As Variant, _
                ByVal StartDate As Variant, _
                ByVal EndDate As Variant, _
                ByVal Status As Variant) As String
    If Left(Status, 7) = "Current" Then
        If IsDate(StartDate) Then
            '--- convert from String to Date if necessary
            StartDate = IIf(TypeName(StartDate) = "String", _
                            DateValue(StartDate), StartDate)
            If IsDate(ReferenceDate) Then
                '--- convert from String to Date if necessary
                ReferenceDate = IIf(TypeName(ReferenceDate) = "String", _
                                    DateValue(ReferenceDate), ReferenceDate)
                If StartDate <= ReferenceDate Then
                    InPost = "YES"
                Else
                    InPost = "NO"
                End If
            Else
                InPost = "ERROR Wrong Format: ReferenceDate"
            End If
        Else
            InPost = "ERROR Wrong Format: StartDate"
        End If
    Else
        InPost = "ERROR: Status is not Current"
    End If
End Function

Function comparing date values

1 Answers1