Automated due date for every Monday
I have several reports that I run. I have a SmartSheet that lists how often it runs (Daily, weekly or monthly). I have an equation that calculates the next due date based on the completion date. The completion date is automatically recorded when I complete the report. It works beautifully except for the times that weekly reports do not run on Monday for whatever reason. Regardless of when I complete a weekly report, I want it to set the next due date for the following Monday unless it is a holiday. Currently it just sets the next due to date to a week from the completion date.
I have a couple of automations that record the Status change date and then resets it and the action status to blanks so that it rolls off of the current due date to the next due date on a report.
Any ideas on how to accomplish this?
Here is my current equation:
=IF(Runs@row = "Daily", WORKDAY([Completed Date]@row, 1, Holidays:Holidays), IF(Runs@row = "weekly", WORKDAY([Completed Date]@row, 5, Holidays:Holidays), IF(Runs@row = "Monthly", WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), 1, Holidays:Holidays))))
Help Article Resources
Check out the Formula Handbook template!