Managing different Holidays calendars



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 !




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!