Microsoft Excel, Microsoft Office, Microsoft Office for Mac, Office 2010, Office 2013, Office 2016, Office 2019, Office 365 /

Multiple Holiday lists in Networkdays()

21 February 2020

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.

Excel’s NetworkDays() and Holidays in the real world

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.

=NETWORKDAYS(Start,End,Holidays)+NETWORKDAYS(Start,End,Vacations)-NETWORKDAYS(Start,End)

=NETWORKDAYS_intl(Start,End,1,Holidays)+NETWORKDAYS_intl(Start,End,1,Vacations)-NETWORKDAYS_intl(Start,End,1)

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)

The catch?

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.

Count your working days in Excel with NETWORKDAYS()

Using NetworkDays.intl to count working days step-by-step

Excel’s NetworkDays() and Holidays in the real world

Excel convert Range to Array in VBA

Excel merge arrays into a single array with VBA

The truth behind Excel dates, time and duration

Simple text with day, month and year to Excel date conversion