calculating difference in dates in excel

[mudstuffin]

Hello.

Can somebody tell me what vba code I can use to calculate the difference between two dates...? I would like a spreadsheet to call a sub if say the dates are older than 3 months from the system date. Also how would this be modified for weeks...?

Thanks in anticipation.

 

[ETID]

Are you wanting to trigger a macro from the result of a worksheet formula?


if not .... the =datedif(startdate,enddate,qualifier)
will return your months, days or years depending on the qualifier ( "M", "D" or "Y&quot

 

[mudstuffin]

Thanks for the reply.

I do want to trigger a sub to alert the user that it is older than 3 months and needs reviewing.

I was intending on using the trigger on either workbook open or after a cell update. I considered using the if statement but not sure how to do with this with subtracting the now() date from the date input into the spreadsheet.

 

[amm9290]

If you had a hidden (or out of sight) cell on your worksheet you could do something like this one-liner:

=IF((EDate(SomeDateDataPoint,3)< TODAY()),CallWarning())

where SomeDateDataPoint is your &quot;old&quot; date and
3 is number of months from the &quot;old&quot; date.
This would trigger upon opening the workbook becasue of TODAY().

function CallWarning()
Beep
msgbox &quot;Caution: Dates old. Re-evaluate&quot;
end function


EDate is not available in VBA, in Excel '97 at least.

But, you could also call this from code if you wanted to be sneaky:

Let SomeDateDataPoint be some hidden or out of sight cell
On WB_Open, let SomeDateDataPoint = &quot;OldDate&quot; cell

In code,
Range(&quot;SomeDateDataPoint&quot.Value = AnoterDateOrDateCell