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?
Holiday Date Sheet: https://app.smartsheet.com/b/publish?EQBCT=51241a1ad0be4024bd2541d302a0d9eb
Help Article Resources
Check out the Formula Handbook template!