Complete Excel NetworkDays() solution with holidays & vacations
Excel’s NetworkDays() and NetworkDays.Intl() need more help to cope with realistic situations with public holidays and more than one vacation.
Annabelle Almer started looking at NetworkDays() & NetworkDays.Intl() and we ended up deep down a geeky rabbit hole trying to get the functions working in realistic situations. The functions work, as advertised, but trying to use them hits limitations in the function’s design and some peculiarities of Excel.
We’ve published the three Excel user-defined functions MergeArray() , Range2Array() and DateListArray() which combine to make NetworkDays() and WorkDays() effective and practical. The same Excel UDF’s (user defined functions) can be used in other workbooks.
Here’s a common Networkdays() realistic scenario, it uses public holidays plus more than one vacation. The table calculates the working days, without weekends, without weekends and public holidays and finally ‘all in one’ with weekends, public holidays and vacations.
In theory, NetworkDays() and it’s sister function Workday() work fine with options to enter ‘Holidays’ as a list of non-workdays. There’s a parameter to accept a list (array) of ‘holidays’.
=NETWORKDAYS.INTL (start_date), end_date, [weekend], [holidays])
More than one ‘holiday’
But a single holidays list is not how the world works. There are public holiday lists plus one of more vacations to consider.
To make things more difficult, there’s no simple way for Excel to combine different lists (ranges or arrays) into a single list for functions like Networkdays() or Workday() can accept.
In the end we dug around and found solutions, really workarounds, to make Networkdays() and Workday() less annoying and more useful.
Combine arrays
First we found some VBA code to merge Excel arrays into a single array.
That’s necessary to combine multiple vacations (each with a list of vacation days) and public holidays into a single array.
See MergeArrays() a custom Excel UDF (user defined function) which can combine dates into a single array for the [holidays] parameter of Networkdays().
Range to Array
Since Public Holidays are usually a range of separate dates in a table, there’s also a need for a ‘range to array’ function.
Networkdays() and Workday() can accept either a range or an array … but not both.
That’s why we’ve detailed Range2Array() another, tiny, Excel UDF which converts a Excel range into an array. It’s annoying that modern Excel makes a distinction between ranges and arrays in many situations. Especially since, as you can see from Range2Array(), the conversion is so damn simple!
We’ve kept Range2Array() separate so you can see our working and also, perhaps, use the function in other situations. If you wish, update MergeArrays() to accept both ranges and arrays, convert where necessary and return a single merged array.
Date List Array
The vacations have a start and end date, NetworkDays() needs a complete list of each vacation day.
In Excel 365 the Sequence() function makes this easy. For other and earlier Excel’s some VBA is needed.
We made DateListArray(), another Excel UDF to do the job.
For Excel 365, the code uses Sequence(). We could use Sequence() directly in Networkdays() but the UDF makes the in-cell formula easier to read.
For Excel 2019 and earlier, change the UDF as shown to do the same job in a different, compatible way.
Single list / array of non-working days
Those three functions let us make a single ‘Holiday’ or non-working day list like this …
MergeArrays( DateListArray(Vacation1_Start,Vacation1_End), DateListArray(Vacation1_Start,Vacation1_End), Range2Array(Holidays[Date]) )
That combines into a single array MergeArray() , all vacation days DateListArray() from two vacations plus the individual public holiday days Range2Array().
Holiday list duplications
Networkdays() and Networkdays.Intl() will automatically de-duplicate the holidays list. There’s no risk of a non-working day being counted twice (e.g a public holiday that’s also a vacation day). In Excel 365 you could use Unique() to remove duplicates but there’s no need for Networkdays().
Complete Networkdays() or Networkdays.Intl()
Bring it all together into a worksheet like this. It calculates the working days of each month from the 1st (@[WorkingDays]) to the last day (EOmonth()). Weekends are Sat and Sun (“1”) then Merge Arrays bring together the different dates and ranges into a single list.
=NETWORKDAYS.INTL( [@[Working Days ]], EOMONTH([@[Working Days ]],0), 1, MergeArrays( DateListArray(Vacation1_Start,Vacation1_End), DateListArray(Vacation1_Start,Vacation1_End), Range2Array(Holidays[Date]) ))
Using NetworkDays.intl to count working days step-by-step
Excel’s NetworkDays() and Holidays in the real world
Excel merge arrays into a single array with VBA
Excel convert Range to Array in VBA
Excel making list of days between two dates
Excel trap in date calculations
The truth behind Excel dates, time and duration