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


Thanks, Lisa

Best Answer

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    edited 06/20/23 Answer ✓

    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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!