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
-
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"))))
Answers
-
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"))))
-
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.
-
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")))))
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives