Managing different Holidays calendars

Hi,

In my schedule, I would like to manage different holidays calendars associated to different department.

In this example, I have 2 Departments to manage : "Dept1" and "Dept2"

I have created a specific "Holidays" sheet to manage the different holidays for each departement with a date column called 'Dept1' to list all of the holidays dates for the 'Dept1' and another date column called 'Dept2' to list all of the holidays dates for the 'Dept2'

In another sheet to manage the schedule of my project, I'm using the WORKDAY formula to calculate the End date based on a 'Start' date column and a 'Duration' column. In this same sheet (to manage the schedule of my project), I have also a column to select the Dept (Dept1 or Dept2) associated to the task

I could take into account the holidays for each departement in my WORKDAY formula using this (assuming that the sheet reference names are named Dept1 and Dept2 in my Holidays sheet):

=IF(Dept = "Dept1", WORKDAY(Start@row, Duration@row - 1, {Dept1}), IF(Dept = "Dept2", WORKDAY(Start@row, Duration@row - 1, {Dept2}), WORKDAY(Start@row, Duration@row - 1)))

It works but it's not really efficient and easy to maintain if I would like to manage more Departments...

Is there a way to improve the formula to update the [holidays] arguments in the WORKDAY formula based on the Dept column values (Dept1 or Dept2 in this example) to reference to the right holidays dates dynamically ?

Thanks in advance for your insights !

Sam

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would suggest nesting the IF inside of the WORKDAY function instead of writing multiple WORKDAY formulas inside of a nested IF.

    =WORKDAY(Start@row, Duration@row - 1, IF(Dept@row = "Dept1", {Dept1}, IF(Dept@row = "Dept2", {Dept2}, {Dept3})))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!