CODEWITHSUNDEEP
vbaexcel
user9730643
user9730643

Reputation: 89

Comparing Dates in vba

enter image description here

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

Upvotes: 2

Views: 6778

Answers (2)

David Zemens
David Zemens

Reputation: 53623

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 
    ...

Upvotes: 2

Vityata
Vityata

Reputation: 43585

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.

Upvotes: 0

Related Questions