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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives