Here’s a limited solution to the problem of excluding multiple holidays or vacation lists to use in NetworkDays() or NetworkDays_Intl() and a request to Microsoft for an improvement in the function.
NetworkDays() or NetworkDays_Intl() is OK if you only have to deal with one list of public holidays or one vacation.
But in the Real World™ people have both public holidays AND vacations. Worse, some lucky folks have more than one vacation in their future.
This is where NetworkDays() becomes hard to handle. There’s only one Holidays parameter into which all the ‘off’ days have to be listed.
It’s surprisingly difficult to combine different lists or arrays into a single array. There’s no in-built function to do it. Plus there are Excel ranges as well as arrays that might need combining.
We looked around the Internet for solutions and the best one we found was here. This is a nicely elegant solution but with a crucial limitation.
In other words, get the number of working days excluding Holidays add to the working days excluding Vacations – then subtract the base work days (duplicated in the first part of the formula)
If there are any overlapping holiday/vacation days, those days are counted twice. In other words, if a public holiday falls during a vacation.
What’s needed is a way to combine the lists and arrays of dates into a single array that NetworkDays() can accept. There’s no simple way to do that, even in Excel 365, which is disappointing.
We have Excel VBA code combining arrays and ranges into a single array which is a useful thing in Excel and not just for NetworkDays().
NetworkDays wish list
Ideally, NetworkDays.Intl() would get a revamp by Microsoft to make it a lot easier to define multiple non-working day ranges. Trying to merge multiple ranges into a single parameter is unnecessarily difficult to setup and maintain.
Perhaps the function could be expanded to accept two or more additional ‘Holidays’ parameters instead of just one. The revised syntax would look like this.
=NETWORKDAYS.INTL (start_date), end_date, [weekend], [holidays], [holidays], [holidays])
With that change, it’s simple to link with different ranges of non-working days: public holidays in one, personal vacations in another etc.
Or, Microsoft could add some array management functions. Combining arrays into a single array is such a common Excel question that is begging for an in-built solution.