Setting up a weekly automation to alert someone in 30, 60 and 90 days of a date

Mike.Jennings56876

I have a sheet that tracks employees certifications. I'd like to setup a single automation that runs weekly to alert the employee when the expiration date is within 180, 90, 60 and 30 days away. I'd prefer that the 180 day alert only be sent monthly until the 90 day date is hit, then weekly. I'd like the message sent to reflect the time left before the certification expires or at least the 180, 90, 60 or 30 day range.

I have a basic automation built that is working but it's triggering in the appropriate date ranges or triggering for all of them.

Paul Newcome

I would suggest a helper column that uses a nested IF to output which alert should be sent (or blank if no alert is to be sent). Then you can set your automation up to run weekly and use a condition of this helper column being in that particular range.

=IF([Expiration Date]<= TODAY(30), "30 Days", IF([Expiration Date]@row<= TODAY(60), "60 Days", IF([Expiration Date]@row<= TODAY(90), "90 Days", IF([Expiration Date]@row<= TODAY(180), "180 Days"))))

Paul Newcome

I would suggest a helper column that uses a nested IF to output which alert should be sent (or blank if no alert is to be sent). Then you can set your automation up to run weekly and use a condition of this helper column being in that particular range.

=IF([Expiration Date]<= TODAY(30), "30 Days", IF([Expiration Date]@row<= TODAY(60), "60 Days", IF([Expiration Date]@row<= TODAY(90), "90 Days", IF([Expiration Date]@row<= TODAY(180), "180 Days"))))

Mike.Jennings56876

Thanks @Paul Newcome, that worked great. How would I handle a blank expiration date? The sheet tracks achieved certs and planned certs, the latter don't have expiration dates yet.

Paul Newcome

Assuming you would want rows with blank expiration dates ignored by the automation, you can start your IF statement off like this:

=IF([Expiration Date]@row <> "", IF([Expiration Date]<= TODAY(30), "30 Days", IF([Expiration Date]@row<= TODAY(60), "60 Days", IF([Expiration Date]@row<= TODAY(90), "90 Days", IF([Expiration Date]@row<= TODAY(180), "180 Days")))))