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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!