Help setting up alerts on a sheet based around certain dates
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
-
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
-
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!
-
Oh, yeah this is perfect. Thanks.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!