Formula to get a Reminder Alert based on Expiry and Notice period

[Hazra Hadee]

Hi,

I have a contracts sheet which has start and end date of the contracts plus the notice period for renewing each of them. I have another column where I want to have a reminder Action to show up saying Action required or No action required based on the expiry and notice period.

Currently I have a formula based on expiry date plus 120 days which give me the alert.

=IF(AND([@[Expiry Date]]>TODAY(),[@[Expiry Date]]<=TODAY()+120),"Send Renewal Reminder","No Action")

However, not all contracts have 3 month notice period. Some have 6 months. So I am not sure how to combine it with the Notice period plus add 20 days to it. Thus I would get an alert 20 days before the notice period starts.

Appreciate the help.

Best Regards

 

[NARAYANK991]

Hi ,

Something like this :

=IF(AND([@[Expiry Date]]>TODAY(),[@[Expiry Date]]<=TODAY() + [@[Notice Period]] + 20),"Send Renewal Reminder","No Action")

The notice period would have to be in days for the above to work ; if the notice period is in months , you can probably multiply it by 30.

Narayan

 

[Hazra Hadee]

Thank you, will try and update

 

[Hazra Hadee]

Thank you Narayank991! Yes, that formula is working as I wanted.

Appreciate the help.

Best Regards
hazra