EXCEL VBA: comparing day & month only from all the date

Question

I am trying to compare only day from the below interval:

Sheets("DATA").Range("H:H").NumberFormat = "mm/dd/yyyy hh:mm am/pm"

I found myself a solution with this IF function that is included in many other IF's

If Sheets("Data").Range("i" & i).Value <> "N/A" And Sheets("Data").Range("K" & i).Value <> "N/A" Then
    If Day(Sheets("Data").Range("i" & i)) = Day(Sheets("DATA").Range("K" & i)) Then
        Sheets("Data").Range("S" & i).Value = "Equals"
    ElseIf Day(Sheets("Data").Range("i" & i)) > Day(Sheets("DATA").Range("K" & i)) Then
        Sheets("Data").Range("S" & i).Value = "Later"
    Else: Sheets("Data").Range("S" & i).Value = "Earlier"
    End If
    Else: Sheets("Data").Range("S" & i).Value = "N/A"
End If

That seems to do the trick - compare only day from the all date interval and write in column S is it the same day, later, or earlier, or "not available".

However, this didnot not work if one or another date is in another month (as there are high volumes more than 500k rows of data from 2012-2013)

How can I compare year/month/date from this format "mm/dd/yyyy hh:mm am/pm" without - deleting hours and minutes from the original row (creating new row without hours/minutes is also not an option)

Thanks!

What about doing date without time in column I then using that for your comparison? — Myles
I am comparing these dates in more than 10 scenarios where I need the hours and minutes. This is one scenario where I just need to tell if it is on the same day, earlier or later — user3812753
I'm not saying alter column H, I'm saying include an additional column that only has your date and not the time. Alternatively do this as a loop, assign the cell value to a variable and alter the format of the variable each time through the loop. — Myles
Something like For i = 1 to 500,000 x=cells(i,8).date {comparison] next i — Myles

Ross McConeghy Ross McConeghy · Accepted Answer · 2014-09-08T22:21:41

Compare the values of the cells using the Value2 property. The Value2 property returns the unformatted value of the cell. Each integer of a stored date/time value represents one day so I've used the Int() method to truncate the stored value so we are only comparing by date, not minutes or seconds.

If Sheets("Data").Range("i" & i).Value <> "N/A" _
        And Sheets("Data").Range("K" & i).Value <> "N/A" Then

    Select Case Int(Sheets("Data").Range("i" & i).Value2)
        Case Is = Int(Sheets("DATA").Range("K" & i).Value2)
            Sheets("Data").Range("S" & i).Value = "Equals"
        Case Is > Int(Sheets("DATA").Range("K" & i).Value2)
            Sheets("Data").Range("S" & i).Value = "Later"
        Case Else
            Sheets("Data").Range("S" & i).Value = "Earlier"
    End Select

Else
    Sheets("Data").Range("S" & i).Value = "N/A"
End If

EXCEL VBA: comparing day & month only from all the date

1 Answers