Need help sending out an update request if modified date is not within 5 business dates of due date

hannahfisher06

I am trying to use the workday function combined with the update request in order to send out an email to whoever needs to update that row. The update request only needs to go out 5 business days before the 15th and additionally 1 business day before the 15th (only if they haven't updated it in that time frame). I have a modified column so I can see when they last updated it but I am struggling with what formula to use?

Soumitra Bhowmick

If you are using Smartsheet's inbuilt Modified date column, it can fail as any change made to that row by anyone would be captured as a modification. Here's what I have used in few of my projects, a bit complicated and there might be some easy way as well but it is working for me as of now

1. Capture the modify date using automation where trigger should be any change made to the column which is supposed to be updated by the designated person. I use this if clause to check if the person has deleted any thing, that shouldn't be considered as a modificaiton

2. In a Date column calculate the 5th working day prior to 15th of the current month and check if that day is greater than Update date. Basically if the person has already updated before 5th working day, there is no need to send any reminder (assuming that is the requirement)

Formula: =IF(WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 15), -5) > [Update date]@row, "", WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 15), -5))

3. Create automation with a trigger similar to below to send updat request. Adjust the time of sending email.

4. Recreate step 2 to create date 1 working day prior to 15th of current month with below formula

Formula: =IF(WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 15), -1) > [Update date]@row, "", WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()), 15), -1))

5. Recreate step 3 on date column created in step 4.