We looked at the handy NetworkDays.Intl() function in Excel and mentioned that the Holidays parameter is a lot more complicated that it appears. Let’s look at how Holidays works and how it gets messy when faced with real world applications.
Quick Recap of NetworkDays.Intl()
Just to recap, NetworkDays.Intl() lets Excel count the number of working days between two dates. NetworkDays() has been in Excel for a long time but the newer NetworkDays.Intl() is the better and more flexible choice.
Count your working days in Excel with NETWORKDAYS()
Using NetworkDays.intl to count working days step-by-step
All you have to do is choose a Start and End date and NetworkDays.Intl() will work. The other two parameters are options.
Weekend defaults to 1 for Saturday and Sunday but you can choose any pattern of working/non-working days.
Holidays is a range or array of dates to exclude from the calculation. This is where the Excel ‘fun’ begins.
What are Weekends?
For NetworkDays.Intl() a ‘weekend’ is any non-workday defined by a regular seven-day week pattern.
Any other non-workday is controlled by the ‘Holidays’ parameter.
What are Holidays?
What Excel and NetworkDays.Intl() call ‘Holidays’ is really a list of non-working days outside a weekly pattern. Individually they can include:
Public Holidays
Vacations
Work closed days (e.g some factories close for refitting, over Xmas/New Year etc).
Really ‘Holidays’ is any day that isn’t a workday, except for ‘weekends’.
Public Holidays
Listing Public Holidays is pretty simple.
You could list them in the function (see the array in the image above) but it’s easier to manage from a separate list or table.
In this example, non-working days are listed in a ‘Holidays’ table with the reference Holidays[Date] . It could have been E8:E13 or even as individual dates.
Some of the holidays are calculated from the ‘Covering the year’ cell E1 but others (shaded in blue) must be manually updated for each year.
Vacations or range of dates
To exclude vacation dates or any range of dates takes a little more work.
Excel novices might think it’s easy, just specify a start and end date for the vacation. Sadly Excel doesn’t work like that, a range like Vacation_Start:Vacation_End only returns two values not a full list of the dates that occur between the two.
The range E12:F12 (start and end days of vacation) only returns two dates, see the serial date values in the function arguments dialog.
Getting all the days between two dates
If you’re using Office 365 with dynamic array support, the Sequence() function makes it easy. Sequence() creates an array of values which increment from a start, to as many as you need.
This formula returns and array of all the dates between start and end dates.
SEQUENCE(1+Vacation_End-Vacation_Start,1,Vacation_Start)
Here’s how it looks in the worksheet:
March has fewer working days because all the days from the Vacation_Start are now listed separately.
Pre Office 365 days between dates
Before Office 365 and dynamic arrays a more fearsome formula is necessary. There are many solutions to the ‘list all day between two dates’ but this one seems the best.
=Vacation_Start+ROW(OFFSET(Vacation_Start,,,Vacation_End-Vacation_Start+1,1))-ROW(Vacation_Start)
The formula begins with the start date, then adds to that based on the row() number and offset(). The result is an array of serial date values (Column H) or as displayed dates (Column I).
In practice, you would not bother showing the days in the worksheet. Either paste that entire formula into the Networkdays.Intl() function or (better) make the formula a defined name.
That’s great if you have a single list of non-working days. Combining multiple vacations/holidays into a single NetworkDays() formula is possible but more difficult. It’ll be the next topic in this series.
Count your working days in Excel with NETWORKDAYS()
Using NetworkDays.intl to count working days step-by-step
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
