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

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.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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"))))