Calculate and alert when Expiration Occurs
I need my sheet to send me an auto alert 30, 60, and 90 days prior to a COI expirees. Formulas I have are not working.
Answers
-
I would create a helper column. In the example formula below I have a column named "Expiration Date" as a date column, and a column named 30/60/90 as a text/number column. The formula will return 0 for anything that is not equal to exactly 30, 60 or 90 days after "Today()". for expiration dates at 90 days out it will return the number 1, for expiration days at 60 days out will return the number 2 and 30 days out the number 3. You can then use a workflow to trigger an alert based on this number changing in the helper column.
=IF([Expiration Date]@row - TODAY() = 90, "1", IF([Expiration Date]@row - TODAY() = 60, "2", IF([Expiration Date]@row - TODAY() = 30, "1", "0")))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!