Automated due date for every Monday

Options
✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!