Holiday list for Networkdays using Join-Collect?
Hello
I have a use case where I need to calculate Networkdays using holidays that are dependent upon location of the resource. I am trying to avoid nested IF statements if possible. I am attempting to populate the [Holidays] field using a Join-Collect function combo to a single list of holidays for all locations. I am getting an error I suspect from the format of the holiday list that Join-Collect produces. Any thoughts?
Best Answer
-
I just tried it with only the COLLECT function and it worked!
=NETWORKDAYS([Period Start]@row, [Year Ending]#, COLLECT({2023 Holidays}, {Holidays Location},[Location]@row))
Answers
-
Can you post your formula and the error please?
Smartsheet Solutions Architect
www.adapture.com
-
Thanks Ramzi...here is what I'm attempting:
=NETWORKDAYS([Period Start]@row, [Year Ending]#, JOIN(COLLECT({2023 Holidays}, {Holidays Location},[Location]@row),", "))
Result: #INVALID DATA TYPE
-
Yes. The JOIN function will output a text string, but the NETWORKDAYS function requires date type values in that field. How many different locations do you need to account for?
-
5 office locations with different national holidays
-
I would create a table with 5 date type columns then and use an IF statement to pull in the appropriate set of dates.
=NETWORKDAYS([Period Start]@row, [Year Ending]#, IF(Location@row = "A", {Table Sheet Location A Date Column}, IF(Location@row = "B", {Table Sheet Location B Date Column}, .............))))))
-
Thanks Paul...I appreciate the help on this. I did get the nested IFs approach to work. Someday hope to have some sort of array function capability to streamline.
-
I just tried it with only the COLLECT function and it worked!
=NETWORKDAYS([Period Start]@row, [Year Ending]#, COLLECT({2023 Holidays}, {Holidays Location},[Location]@row))
-
Thank you Paul! I've just returned from vacation and gave this a try and it solved for a very long formula! Very much appreciated!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!