How to find the difference between dates in VBA
I wonder why I rarely see people using the date functions.
You can also use this:
if DateDiff("d", date1, date2) > 30 then
in this case, date1 would be CDate(Worksheets("dates").Cells(1,1))and date2 would be sdate (either cast with CDate or dim'd as a date as Jeff said.
"d" means we are getting the difference in days. Here are the intervals for years, months, etc. in VBA:
yyyy - Yearq - Quarterm - Monthy - Day of yeard - Dayw - Weekdayww - Weekh - Hourn - Minutes - Second
sDate is a 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 caveat in this kind of conversion. These conversion will handle the following structures:
yyyy/mm/ddyyyy/m/dmm/dd/yyyym/d/yyyy
These will not be correctly converted
dd/mm/yyyyd/m/yyyy
And avoid using any 2-digit year.
I would advise using the DateSerial() function for date conversion.
So regarding your code, assuming that the values on yor sheet are actually dates (to be certain, simply select the column and change the Number Format to GENERAL. If they are real dates, each will display a PURE NUMBER. Remember to hit UNDO to get your Date Format back)
Dim result As BooleanIf Worksheets("dates").Cells(1, 1).Value - Date > 30 Then result = TrueElse result = FalseEnd If