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


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

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.

excels networkdays and holidays in the real world microsoft office 35040 - Excel’s NetworkDays() and Holidays in the real world

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.

excels networkdays and holidays in the real world microsoft office 35041 - Excel’s NetworkDays() and Holidays in the real world

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.

excels networkdays and holidays in the real world microsoft office 35042 - Excel’s NetworkDays() and Holidays in the real world

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:

excels networkdays and holidays in the real world microsoft office 35043 - Excel’s NetworkDays() and Holidays in the real world

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)

excels networkdays and holidays in the real world microsoft office 35044 - Excel’s NetworkDays() and Holidays in the real world

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

subs profile e1563205311409 - Excel’s NetworkDays() and Holidays in the real world
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