CODEWITHSUNDEEP
vbaexcel
HopelessN00b
HopelessN00b

Reputation: 452

How to determine if a date is in the past?

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

Upvotes: 6

Views: 86029

Answers (3)

Troudouillet
Troudouillet

Reputation: 1

I follow your answer and No ! No ! And No!

On Excel VBA, It's wrong ! you need to format the date like "2020/07/18 09:48:51" to can compare

So first get Date variable with CDate() function, and after, you can compare like that. Else it's not working.

If Format(MyFileDate, "yyyymmdd hhnnss") >= Format(ReportStart, "yyyymmdd hhnnss") And Format(MyFileDate, "yyyymmdd hhnnss") < Format(ReportEnd, "yyyymmdd hhnnss") Then

Upvotes: 0

S aziagba
S aziagba

Reputation: 53

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

Upvotes: 5

John Bustos
John Bustos

Reputation: 19574

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

Upvotes: 17

Related Questions