CODEWITHSUNDEEP
vbaexcel
Mikz
Mikz

Reputation: 591

Compaing dates with VBA

I am comparing two Dates in column W and AA. The case is if Column AA > W, then it should print Nok. If column AA <= W , then it should print OK.

I have my code working. but the Problem is equal to condition is not satisfied.

For eg I have a date 01.09.2017 in column W and AA, According to the formula it should be Ok, but it prints NOK.

Could anyone tell me , what is wrong with the condition. I want it to do in VBA. 

Sub Compare1()

    Dim i               As Long
    Dim lngLastRow      As Long
    Dim ws              As Worksheet            

    Set ws = Sheets("BW")    
    With ws            
        For i = 2 To 591
            If .Cells(i, 27).Value = "" Then            
                .Cells(i, 28).Value = "N/A"
            Else            
                If .Cells(i, 27).Value <= .Cells(i, 23).Value Then            
                    .Cells(i, 28).Value = "OK"
                    .Cells(i, 28).Interior.Color = RGB(0, 255, 0)            
                Else                
                    .Cells(i, 28).Value = "NOK"              
                    .Cells(i, 28).Interior.Color = RGB(255, 0, 0)
                End If            
            End If                
        Next i
    End With

End Sub

Upvotes: 0

Views: 73

Answers (3)

Shai Rado
Shai Rado

Reputation: 33692

You can use the DateDiff function to get the Delta between 2 dates.

When putting "d" as the first parameter, you are checking the delta in days, so if both dates are the same, just the hourse ar different, the result will still be 0.

To learn more about DateDiff function go to MSDN

Code 

Sub Compare1()

    Dim i               As Long
    Dim lngLastRow      As Long
    Dim ws              As Worksheet
    Dim DeltaDays       As Long

    Set ws = Sheets("BW")
    With ws
        For i = 2 To 591
            If .Cells(i, 27).Value = "" Then
                .Cells(i, 28).Value = "N/A"
            Else
                DeltaDays = DateDiff("d", .Cells(i, 27).Value, .Cells(i, 23).Value)
                If DeltaDays <= 0 Then
                    .Cells(i, 28).Value = "OK"
                    .Cells(i, 28).Interior.Color = RGB(0, 255, 0)
                Else
                    .Cells(i, 28).Value = "NOK"
                    .Cells(i, 28).Interior.Color = RGB(255, 0, 0)
                End If
            End If
        Next i
    End With

End Sub

Upvotes: 2

YowE3K
YowE3K

Reputation: 23994

If your columns W and AA contain dates with times, the easiest fix is to just remove those times by taking the integral part of the date/time value.

So you can use:

If Int(.Cells(i, 27).Value) <= Int(.Cells(i, 23).Value) Then

Just be aware that this will be deciding that a date/time of, for instance, 10:00am on 28 June 2017 is less than or equal to 7:00am on the same date, which sounds like what you are trying to do - but it does raise the question of why the times are in the cells if they are just going to be ignored anyway.

Upvotes: 0

Mr.Burns
Mr.Burns

Reputation: 700

Something like this should do it for you, your issue ,like the comments state, is that you are likely passing the time in your dates, the Format function will allow you to remove them, The reason for On Error Resume Next is that if the cells value is blank is will give the error 13 - Type Mismatch

Sub Compare1()

    Dim i As Long, lngLastRow As Long
    Dim ws As Worksheet
    Dim FirstDate As Date, SecondDate As Date

    Set ws = Sheets("BW")

    With ws

        For i = 2 To 591

            On Error Resume Next
            FirstDate = Format(.Cells(i, "AA").Value, "dd/mm/yyyy")
            SecondDate = Format(.Cells(i, "W").Value, "dd/mm/yyyy")
            On Error GoTo 0

            If FirstDate = Empty Then
                .Cells(i, 28).Value = "N/A"
            Else
                If FirstDate <= SecondDate Then
                    .Cells(i, "AB").Value = "OK"
                    .Cells(i, "AB").Interior.Color = RGB(0, 255, 0)
                Else
                    .Cells(i, "AB").Value = "NOK"
                    .Cells(i, "AB").Interior.Color = RGB(255, 0, 0)
                End If
            End If

        Next i

    End With

End Sub

You can also use the below instead of the If statement, I personally feel it is simpiler and clean but that is personal preference

Select Case FirstDate
    Case Is = ""
        .Cells(i, "AB").Value = "N/A"
    Case Is <= SecondDate
        .Cells(i, "AB").Value = "OK"
        .Cells(i, "AB").Interior.Color = RGB(0, 255, 0)
    Case Is > SecondDate
        .Cells(i, "AB").Value = "NOK"
        .Cells(i, "AB").Interior.Color = RGB(255, 0, 0)
End Select

Upvotes: 1

Related Questions