Using NETWORKDAYS to calculate the number of business days between two dates in Excel

NETWORKDAYS (That’s Net Work Days, not Network Days) is a versatile spreadsheet function that lets you calculate working days between two dates, allowing for weekends, public holidays and more.

[Excel 2010, 2013, 2016, Google Sheets]

In Excel, calculating the difference between two dates is very simple – you just minus one from the other. This is because all dates are simply a representation of a serial date ID.

However, sometimes you might need to know how many working days there are between two dates. In this walk-through, we’ll go through a function which can help you work this out – but also offers a few extra tricks up its sleeve. This function is also valid for use with Google Sheets.

Net Working Days

The function we’re going to be using is NETWORKDAYS()

The first thing you are likely to see is that there two versions of this function.

NETWORKDAYS()

NETWORKDAYS.INTL()

We’ll work with NETWORKDAYS.INTL, as this is the newer version, but the only difference is that the new version allows us to define what is classed as a weekend.

There are 4 parameters needed for NETWORKDAYS.INTL. We’ll start with the two required ones.

START DATE (Required) – The date you want to count from

END DATE (Required) – The date you want to count to

It’s important that the dates you use are in the correct syntax / are recognised as valid dates by Excel. For this reason, Microsoft recommend that you use the DATE() function to build your date field up.

But of course you can do this in your reference cell rather than directly in the function.

Choosing Weekend Behaviour

As you will have seen, the optional third parameter allows you to define which days should be treated as non-work days. (If you use the legacy version of NETWORKDAYS, this option is not available to you)

As you begin to enter the parameter, you will be offered a list of configurations to choose from. Once you have chosen, your selection will be identified as an integer number.

Note: If you do not specify a value for WEEKEND, it will default to 1 (Saturday, Sunday). However, I would advise you to always specify what you want to avoid confusion.

If the weekend combination you are after does not exist (perhaps you work part-time, on a 3 day week), you can create a custom string to highlight which days should, or should not, be included. Starting with Monday, uses a series of 1’s + 0’s to set whether than day is a working day or a weekend day. (1 = Non-work day, 0 = work day)

This will set Monday, Saturday and Sunday as non-working days

Including Holidays

The fourth parameter is a very powerful, and often overlooked, optional parameter allows you to give a further range of dates you want to exclude in the calculation.

This might be a list of public holidays in a specific region, or if you’re building a resource planner, then perhaps it links to a list of personal holidays.

The easiest way to do this is creating a separate list of your dates, and give it a named range, or convert to an Excel Table

You can then simply reference this range in your formula.

Points to Consider

There are a couple of other behaviour points to consider.

1)    If your START and END dates are during the same ‘close’ period (eg, Saturday & Sunday where Sat & Sun is your weekend), the function will return 0 days.

2)    If your START and END date is the same date, it will return 1 (unless it falls on a weekend day). If you want it to return 0 in this scenario, we can achieve this by minus-ing the SIGN of the result from the original result.