Holiday list for Networkdays using Join-Collect?

Teri2g

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?

Paul Newcome

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

Ramzi

Can you post your formula and the error please?

Teri2g

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

Paul Newcome

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?

Teri2g

5 office locations with different national holidays

Paul Newcome

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

Teri2g

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.

Paul Newcome

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

Teri2g

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!

Paul Newcome

Happy to help. 👍️