Calculate and alert when Expiration Occurs

Options

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

  • JamesB
    JamesB ✭✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!