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
Answers
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!