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
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 352 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!