6

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
Community
  • 1
  • 1
HopelessN00b
  • 452
  • 2
  • 8
  • 22

3 Answers3

17

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

John Bustos
  • 19,036
  • 17
  • 89
  • 151
  • Looks like it leaves me with the same problem - CDate seems to convert a date into a format where dates in January of this year are still less than dates in December of last year. – HopelessN00b Jan 02 '13 at 17:17
  • ... Can you explain a bit more and maybe show what you have in the cells you're converting to dates? Once you use `cDate` you're doing a date comparison, so January of this year will be greater than any date before that... – John Bustos Jan 02 '13 at 17:21
  • Oh, my apologies, that does work after all... I just had to use it correctly. – HopelessN00b Jan 02 '13 at 17:31
  • You need to compare dates to dates and times/timestamps to other times/timestamps. If not, then **8:30 This Morning** > **Today** = **TRUE**. "DateValue will return only the date. CDate will preserve the date and time." [Difference between DateValue and CDate in VBA](https://stackoverflow.com/a/24945235/5640342) – ChrisB Nov 30 '21 at 01:07
5

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
    ...
S aziagba
  • 53
  • 1
  • 6
0

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
Troudouillet
  • 1