Set up an alert when cell is blank after 3 business days

LMallery

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?

Jeff Reisman

@LMallery

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