Comparing Dates in vba

Question

I am having an issue comparing Dates in excel. One date values is pulled from a worksheet and is in the form "24-JAN-17". The other data is declared in the script in from "2017-12-31". Does anyone know a solution to comparing the two dates so I can determine if the date value pulled from the sheet is later than 2018. I have included the code in it's current state below.

Sub removeWrongYear()

Dim i As Long, yearA As Long, rowsCnt As Long
Dim rowsToDelete As Range
Dim vData As Variant

yearA = 2017

With ActiveSheet

    '1st to 635475 row, 20th column
    vData = Range(.Cells(1, 20), .Cells(635475, 20))

    For i = UBound(vData) To 2 Step -1
        If vData(i, 1) > DateSerial(yearA, 12, 31) Then
            rowsCnt = rowsCnt + 1

            If rowsCnt > 1 Then
                Set rowsToDelete = Union(rowsToDelete, .Rows(i))
            ElseIf rowsCnt = 1 Then
                Set rowsToDelete = .Rows(i)
            End If

        End If
    Next i

End With

If rowsCnt > 0 Then
    Application.ScreenUpdating = False
    rowsToDelete.EntireRow.Delete
    Application.ScreenUpdating = True
End If

End Sub

What's the issue you're having? Explain please. Does your vData array actually contain dates, or is it date-like strings? — David Zemens
– David Zemens, Commented May 17, 2018 at 15:24
@DavidZemens I am trying your suggestions right now, do you know how much this will increase the run time of the script? — user9730643
– user9730643, Commented May 17, 2018 at 15:36

David Zemens · Accepted Answer · 2018-05-18 14:36:58Z

You are working with string literal values on the worksheet, which represent dates in a US date format (DD-MMM-YY). These are not Date values, they're just strings. So when you try to parse it like a string like "22-JAN-18" e.g. using the Year function, my understanding is that it should return "2018". But working with different locale settings can be tricky, and this is complicated by the fact that VBA -- despite locale settings -- always (?) interprets dates in US format.

Can you test this:

Sub test()

    Dim s as String
    s = "22-JAN-18"
    Debug.Print Year(s)

End Sub

If that's not working, try:

Debug.Print Year(Format(s, "DD-MMM-YY"))

That may work, because you're explicitly specifying the format of the date-like string.

If that works, then try:

Dim theDate as Date
theDate = DateValue(Format(vData(i, 1), "DD-MMM-YY"))

If Year(theDate) > yearA Then 
    ...

Vityata · Accepted Answer · 2018-05-17 15:29:52Z

0

One date values is pulled from a worksheet and is in the form "24-JAN-17". The other data is declared in the script in from "2017-12-31".

To compare two variables, the best way is to make sure that they are of the same type. Thus, try to parse them as dates. Then compare:

Public Sub TestMe()

    Dim dt1 As Date: dt1 = "24-JAN-17"
    Dim dt2 As Date: dt2 = "2017-12-31"

    Debug.Print Year(dt1) < 2017
    Debug.Print Year(dt1)

End Sub

In your code > DateSerial(2016, 12, 31) is pretty much the same as Year(dt1) < 2017, but taking the year seems a bit easier.

answered May 17, 2018 at 15:29

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

2 Comments

Ryszard Jędraszyk Over a year ago

You're trying to assign a String value to Date type variable, it will produce "Type mismatch".

Vityata Over a year ago

@RyszardJędraszyk - the type mismatch will be only if it cannot be parsed to a date. And this error is a good one, it means that the input should be checked.

Collectives™ on Stack Overflow

Comparing Dates in vba

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related