Formula for Reoccurring Tasks - Different Frequencies
I am looking for creating a sheet where team members can added tasks that happen more than once via a form but am getting stuck on the formula to calculate all of the due dates. here are the scenarios we need covered:
Weekly = 5 business days in between
Weekly - Specific Day of the Week = Due once every week on the same day of the week
Every 2 Weeks = 10 business days in between
Monthly = 20 business days in between
1st Business Day of the Month = 1st BD of the Month each Month
Quarterly = 62 business days in between
Annually = 250 business days in between
In any of these scenarios, when a due date falls on a holiday designated as a non work day (have list referenced via a separate sheet) - I need it to be due the business days prior for that one instance only and then pick right back up as normal after that. I.e. Due every Monday - 5/31 is a non-work day so its due on Friday 5/28 for that week but then the next one is back on schedule for 6/7. Is all of this even possible or do we need to let go of the formula idea and find something to integrate and help us accomplish this? Help?
Source Sheet: https://app.smartsheet.com/b/publish?EQBCT=786b7ea57df64d7f8a13e229eddf19f3
Holiday Date Sheet: https://app.smartsheet.com/b/publish?EQBCT=51241a1ad0be4024bd2541d302a0d9eb
Answers
-
Hi @Emmy Anderson ,
Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
@Bassam Khalil thank you for jumping in :) the link to the published source sheet is here: https://app.smartsheet.com/b/publish?EQBCT=786b7ea57df64d7f8a13e229eddf19f3
the holiday sheet is also linked in my original post towards the bottom, if you need that one.
-
@Emmy Anderson If the purpose of this is just to remind/alert someone for the task to be done on certain dates/regular intervals, then I would recommend using Automation workflow "Alert Someone" which you can setup for multiple scenarios you have and you have lot of flexibility with the dates cadence. That will take care of sending emails to the owners when the conditions are met.
-
@SK - we need the due date to show up on reporting as well as give the individuals a checkbox per instance where the task is due to mark it off as complete. We have the alert someone workflows set up already but its based on the up to 52 individual dates that are calculated on the sheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!