Networkingdays has an optional third parameter for listing holidays to exclude, the trouble is that I cannot seem to figure out the proper way to list more than one holiday. Each time I try to list a second year, month, and day I get some sort of error. Would anyone be able to give me/point me to a functioning networkingdays with more than one holiday listed?
Thank you!
hi @Adam_Romary ,
It took me a bit more than the 5 minutes I had in mind to answer your question because I stumbled upon the issue you mentioned. A single parameter like a list of holidays worked well, but multiple lists not.
In these cases I standard use Contains() because via a comma you say check for values in list 1 OR list 2 OR list 3 and so on. If the value appears in any of the used lists, it is taken into account. However not in this case. I had to apply ListCombine() as you see below, and I don’t know why this works like this in this function.
NetWorkingDays(thisRow.Name,EndOfMonth(thisRow.Name,0),listcombine(HLDYS.From,HLDYS.To))
Enjoy! Christiaan
hmm, I have to add something, the above works obviously because the days are sequential (N & N+1).
However what if you have a holiday that takes 10 days, then the above formula would leave out 8 days, it would only take into account the start & the end date.
Well to solve that I see two options. The easiest one is to create an extra column in the first table that contains all the holidays and you take this list. It looks like this:
Imagine you have huge tables and you do not want to store all the data (because factually you do not need it), you only need to calculate with it. In that case we virtualise the calculation.
I split the process in a few steps:
step 1: 1 look for corresponding holidays
Step 2, I take per holiday the dates (formatted as numbers):
and then the step 3 is to use this list of dates in the formula.
This one fails.
the list of dates is not excluded and I do not know why.
An alternative to this base formula NetWorkingDays is to exclude weekends as day number 1 and 7 and excluding the holidays like below:
This one works.
Cheers, Christiaan
Thank you so much for this help!
good to hear from you again @Adam_Romary , it has been a while 
would you be so kind to mention the contribution ‘as solution’ to close the thread and to enable others to find find inspiration for alike problems?
cheers, Christiaan
Done. Sorry, I didn’t see that option before.
Adam
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
