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?
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.
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.