How to determine if a date is in the past?

Question

I have an Excel workbook for tracking backup success. We have a number of sheets with the date in the first column, and macros to do calculations, based on whether the date is in the past or not. (The macros either hide or reveal the appropriate rows.)

This has been working until yesterday. I assume because the macros are doing a string comparison, rather than a date comparison. ("01/01/2013" is smaller than "12/31/2012", when viewed as strings.)

Is there a native way to compare dates in VBA, or do I need to convert the dates into "yyyy/mm/dd" first (a how to would be nice).

A2 is the cell with the first date we started using this new version of the spreadsheet, and A454 is the last date I extended the spreadsheet to, corresponding to the end of this year.

Sub ShowAll()
    Dim cell As Range
    For Each cell In Range("A2:A454")
        cell.EntireRow.Hidden = False
    Next
End Sub
    
Sub RevealPast()
    Dim cell As Range
    For Each cell In Range("A2:A454")
        If cell.Value < Date Then
        cell.EntireRow.Hidden = False
        End If
    Next
End Sub
    
Sub HideFuture()
    Dim cell As Range
    For Each cell In Range("A2:A454")
        If cell.Value >= Date Then
            cell.EntireRow.Hidden = True
        End If
    Next
End Sub

Answer 1

Try the cDate function.

something along the lines of:

If CDate("02/01/2013") > Date (or Now() if you want today's date) Then
   ...

So, in your example:

If cDate(cell.Value) >= Date Then

I hope I understand your question correctly and hope this helps...

Answer 2

I know this is a bit different from what you asked but this might help someone someday. If there is an hour added to the date (3/5/2014 8:00:00 AM) and you would like to compare with your date you can:

'X being the date you want to compare

x = CDate(x) 'formatting the date using the CDate function

x= Format(x, "MM/DD/YYYY") 'formatting the date by dropping the hour

x= CDate(x) 'formatting the date again 


If x <= Date Then
    ...