How to find the difference between dates in VBA

Question

I am trying to figure out the difference between the system date and the date stored on the sheet. If the difference between them is> 30 days, the result will be true, otherwise the result will be false

Dim result as boolean Dim sDate as string sDate = Date if Worksheets("dates").Cells(1,1) - sDate > 30 then 'how do I do this? result = true else result = false end if 

How to find out the difference in days between the system date and the date stored on the sheet? The date on the sheet may also be a past date.

Answer 1

I wonder why I rarely see people using date functions.

You can also use this:

 if DateDiff("d", date1, date2) > 30 then 

in this case, date1 will be CDate (Worksheets ("dates"). Cells (1,1)) and date2 will be sdate (either with CDate or dim'd as the date, as Jeff said.

"d" means we get the difference in days. Here are a few years, months, etc. In VBA:

 yyyy - Year q - Quarter m - Month y - Day of year d - Day w - Weekday ww - Week h - Hour n - Minute s - Second 

Answer 2

Try the following:

 if CDate(Worksheets("dates").Cells(1,1)) - sDate > 30 then 

Answer 3

sDate is STRING, which is not a real date!

Convert your string to a date using either the CDate () function or the DateValue () function.

However, there is a reservation to this transformation. These transformations will process the following structures:

 yyyy/mm/dd yyyy/m/d mm/dd/yyyy m/d/yyyy 

They will not be converted correctly

 dd/mm/yyyy d/m/yyyy 

And avoid using a two-digit year.

I would suggest using the DateSerial () function to convert the date.

So, regarding your code, assuming that the values ​​on the yor sheet are actual dates (to be sure, just select the column and change the number format to GENERAL. If these are real dates, each of them will display PURE NUMBER. Press UNDO to get the format dates).

 Dim result As Boolean If Worksheets("dates").Cells(1, 1).Value - Date > 30 Then result = True Else result = False End If