Complete Excel NetworkDays() solution with holidays & vacations


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

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.

complete excel networkdays solution for holidays vacations microsoft office 35310 - Complete Excel NetworkDays() solution with holidays & 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.

complete excel networkdays solution for holidays vacations microsoft office 35311 - Complete Excel NetworkDays() solution with holidays & vacations

=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

subs profile e1563205311409 - Complete Excel NetworkDays() solution with holidays & vacations
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