Managing different Holidays calendars

Samuel Leroy

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

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})))