Multiple Holiday lists in Networkdays()


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

multiple holiday lists in networkdays microsoft excel 35054 - Multiple Holiday lists in Networkdays()

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)

Or

=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

subs profile e1563205311409 - Multiple Holiday lists in Networkdays()
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address