Help setting up alerts on a sheet based around certain dates

murphyspccms

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

Jeff Reisman

@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.

Jeff Reisman

@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.

murphyspccms

Oh, yeah this is perfect. Thanks.