Set up an alert when cell is blank after 3 business days
Hi! I need to set up an alert to notifiy a staff member and supervisor if contact is not made within three business days. I am using the Date Added cell and an Initial Contact cell. I want an alert to trigger when the Initial Contact cell is blank on the 4th business day from the Date Added cell. What formula should I use?
Answers
-
Since you're specifying 4 business days, we'll need to set up a helper Date-type column (call it "CurrentDate"?) to determine the current date and a helper Text/Number column (call it "AlertTrigger"?) to contain the value that will trigger the alert.
Set up a new Automation that runs once a day, which records the the date in the CurrentDate helper field. (We're doing this because if we just tried to use the TODAY function in the formula below, it might not always work. This is because the TODAY value does not update unless you open the sheet manually.)
Then, in your AlertTrigger helper column:
=IF(ISDATE([Initial Contact]@row, "", IF(NETWORKDAYS([Date Added]@row, CurrentDate@row) >= 4, 1, ""))
In English: If there's a date in the Initial Contact cell, just leave this AlertTrigger cell blank. If there's not a date in that cell, and the number of business days since the Date Added and the CurrentDate is greater than or equal to 4, set this cell to 1; otherwise, leave it it blank.
Then set up your Alert automation:
Trigger: When rows are changed, When AlertTrigger changes to 1, Run Workflow When triggered.
Condition: Where [Initial Contact] is not a date
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!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives