Formula for Reoccurring Tasks - Different Frequencies

Emmy Anderson

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

Bassam Khalil

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.

Emmy Anderson

@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.

Sameer Karkhanis

@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.

Emmy Anderson

@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.