Help setting up alerts on a sheet based around certain dates

Options
murphyspccms
murphyspccms ✭✭✭✭
edited 02/13/23 in Formulas and Functions

Hey all,

I'd like to set up triggers on my sheet that would follow a claim throughout its lifecycle. The alerts I would like are if the contact date is empty once it's 2 days past received by date, if inspection date is blank once it's 7 days past the contact date, etc.

I was thinking of creating helper columns that would be the received by date plus 2 days, and once the contact column is greater than the helper column, then it would trigger some kind of conditional formatting. I think this would work, but it would require me to make 4 or 5 helper columns, so I was curious to see if there's another way to approach this that's less complicated? Here's the sheet published with all sensitive data removed. https://publish.smartsheet.com/f0c1998e5903481dbdc040d44e2c7b31

Thanks!!


Edit: I was thinking of a formula along these lines, but there's a mistake somewhere in it. I'm still learning Smartsheets

=IF(OR(ISBLANK([Claim - First Contact On]@row), [Claim - First Contact On]@row > [Claim - Received On]@row + 2, "Alert", ""))

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/13/23 Answer ✓
    Options

    @murphyspccms

    Here's the automation trigger setting. Use When a date is reached, select the date field, and click the down arrow next to "on" to schedule it for a certain number of days before or after the value in that date field.

    The values range from 1-6 days before/after, 1-4 weeks before/after, 30, 60, 90, 180, 365 days before/after. Then set the time of day the alert should go out. It's usually sent with about 2-3 minutes of that time.

    Use the Condition block below the trigger to specify other column values that need to be in place for the alert to be triggered.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/13/23 Answer ✓
    Options

    @murphyspccms

    Here's the automation trigger setting. Use When a date is reached, select the date field, and click the down arrow next to "on" to schedule it for a certain number of days before or after the value in that date field.

    The values range from 1-6 days before/after, 1-4 weeks before/after, 30, 60, 90, 180, 365 days before/after. Then set the time of day the alert should go out. It's usually sent with about 2-3 minutes of that time.

    Use the Condition block below the trigger to specify other column values that need to be in place for the alert to be triggered.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • murphyspccms
    murphyspccms ✭✭✭✭
    Options

    Oh, yeah this is perfect. Thanks.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!