OzGrid

How to create VBA code to compare dates

Requirement:

Multiple users update a workbook that contains various information, two of the items are "Original Completion Date" and "Expected Completion Date".

One of the functions of the code the user is writing is to compare these two dates, and depending on the difference set a trend indicator in a new cell. For instance, if the Expected Completion Date is within 2 weeks of Original Completion Date it would turn the cell green. If it were more than 4 weeks it would turn it red.

This is the code that the user currently has:

If (Format(Cells(x, 19), "ww") - Format(Cells(x, 22), "ww")) <= 2 Then 
    Cells(x, 16) = "G"
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If

The dates are entered in the DD-Mmm-YY format. The user figured that converting the dates to the week number and finding their difference would allow the comparison. But the user gets a type mismatch on the If command line.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/149571-vba-date-comparison

Solution:

Dim iDtDif As Integer

If IsDate(Cells(X, 19)) And IsDate(Cells(X, 22)) Then
    iDtDif = DateDiff("ww", Cells(X, 19), Cells(X, 22))
    If iDtDif <= 2 Then
        Cells(X, 16).Interior.Color = vbGreen
    ElseIf iDtDif > 4 Then
        Cells(X, 16).Interior.Color = vbRed
    End If
End If

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery