Automated due date for every Monday

Lisa Vercellone

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))))

Thanks, Lisa

Hollie Green

Incorporate the below into your formula it will give you the Monday following the last completed date.

=[Completed Date]@row - (WEEKDAY([Completed Date]@row) - 2)+7

Hollie Green

Incorporate the below into your formula it will give you the Monday following the last completed date.

=[Completed Date]@row - (WEEKDAY([Completed Date]@row) - 2)+7

Lisa Vercellone

Thank you! It took me a while to get my head around how and why this equation works! I knew there had to be a simple way I wasn't thinking of.