Excel NETWORKDAYS.INTL Function – How To Use

The NETWORKDAYS.INTL function is a Date/Time function in Excel that helps calculate the difference between two given dates in terms of working days. The function, just like the NETWORKDAYS function, automatically omits weekends while computing the number of workdays. In fact, it can do everything that the NETWORKDAYS function does. Plus, it allows you to choose custom weekend days.

Cost accountants, financial analysts, and human resource managers may all find the NETWORKDAYS.INTL function useful for varying purposes. For instance, a cost accountant may need to compute the per-day labor cost of the company that gives Thursdays and Fridays off.

Syntax

The syntax of the NETWORKDAYS.INTL function is as follows:

Arguments:

start_date – This is a required argument. You must enter the date from which you want the workday computation to start.
end_date – This is a required argument. You must enter the date on which you want the workday computation to stop.
weekend – This is an optional argument. By default, the function assumes the argument's value as 1 (i.e., assumes Saturday and Sunday as the weekend). You may use a value (see table below) to customize the weekend or use binary digits.
holidays – This is an optional argument. If you want to use a different workday schedule (the default being Saturday and Sunday considered as weekend days), you may enter a cell range consisting of the list of holidays as an input in this argument.

Weekend Codes and Description

Important Characteristics of the NETWORKDAYS.INTL function

• By default, the NETWORKDAYS.INTL function assigns the value 1 to the [weekend]
• The NETWORKDAYS.INTL function allows you to add a list of holidays using the [holidays]
• The dates entered as start_date and end_date are both included in the calculation of workdays.
• If the start_date contains a date later than the one entered in the end_date argument, the function returns a negative value.
• If the date supplied in any argument is out of range for the current date base value, the function returns a #NUM! error.
• If a date supplied in any argument is invalid or contains invalid characters, the function returns a #VALUE! error.

Examples

Let's try to see some examples of the NETWORKDAYS.INTL function.

Example 1 – Plain Vanilla Formula for the NETWORKDAYS.INTL Function

We'll start by looking at the bare-bones version of the NETWORKDAYS.INTL function. We won't use any holidays, but we will assign a non-default weekend using the [weekend] argument. The formula we'll use is:

Applying this formula to the data returns 8. Let's walk through how Excel calculated this. Before we do that, notice that we've entered the value 6 in the [weekend] argument, which means the function will treat Thursdays and Fridays as the weekend.

In total, there are 12 days between December 25, 2001 and January 5, 2001. Of these, 2 days (December 27 and January 3) are Thursdays and 2 days (December 28 and January 4). Effectively, that leaves out 8 working days (12 – 4).

Example 2 – Non-Consecutive Off Days

In the previous example, we saw how we can set any two days (or any one day) of the week as an off day. This is rather constraining, though. What happens if a company's off days aren't consecutive? Or, how would countries like Denmark, which are on the verge of adopting a four-day workweek, deal with this problem?

Since Danish businesses aren't impressed with the lack of versatility, we must offer them a technique that they can use to continue living a good quality life and make the rest of the world green with envy.

Fortunately, the NETWORKDAYS.INTL function allows full control over setting workdays and weekends. For instance, say you want to give your employees Sundays off, and then give them a mid-week breather on Thursday.

To calculate workdays for this workweek structure, we must use a text string composed of 0s and 1s in the [weekend] argument, where 0s represent workdays and 1s represent off days. To calculate workdays in our example, we'll use the following formula:

Note that the first digit in the text string represents Monday.

Notice how the output changes to 9, from 8 in the previous example. This is because there are 2 Thursdays (December 27 and January 3) and only 1 Sunday (December 30). This gives us 9 (12 – 3) workdays.

As you see, using 0s and 1s allows a lot more flexibility in terms of calculating workdays for businesses with a non-conventional workweek.

Example 3 – Adding Holidays

Let's address the final argument of the NETWORKDAYS.INTL function, the [holidays] argument. The [holidays] argument is just a set of days that are to be treated as off days.

Let's use the same data we used in the previous example, and use the following formula that assigns the [holidays] argument a cell range (F2:F4):

Since we've listed 3 holidays, notice how the output reduces from 9 to 6. If one of the holidays had been either a Thursday or a Sunday, the output would have been 7. NETWORKDAYS.INTL, of course, does not double count.

WORKDAY.INTL vs NETWORKDAYS.INTL

The WORKDAY.INTL function is an extension of the WORKDAY function, much the same way as the NETWORKDAYS.INTL function is an extension of the NETWORKDAYS function.

Therefore, to understand the difference between these extended functions, we must understand the difference between the original functions.

The WORKDAY function calculates N number of working days into the past or future from a given date. On the contrary, the NETWORKDAYS function calculates the difference in terms of working days between two given days.

While the output of the WORKDAY function is a particular date, the output of the NETWORKDAYS function is in terms of number of days.

The addition of the .INTL extension just adds the capability to set custom weekends to both functions.

For instance, let's say you wanted to compute 15 working days into the future with Thursdays and Fridays as the weekend. You'd use the following WORKDAY function formula:

Our output is January 15, 2002. There are, in total, 22 days between December 25 and January 15. This effectively means that there were 7 (22 – 15) off days between these dates. Since we haven't added any holidays, all of the off days must either be a Thursday or a Friday.

Notice how the output is a date and not in terms of number of days. This is how the WORKDAY.INTL function differs from the NETWORKDAYS.INTL function.

NETWORKDAYS vs NETWORKDAYS.INTL

Meet the less capable cousin of the NETWORKDAYS.INTL function. The only difference between these two functions is the customizability of off days. The NETWORKDAYS function has all arguments that the NETWORKDAYS.INTL function has, except for the [weekend] argument.

The NETWORKDAY function, therefore, always assumes the weekend as Saturday and Sunday and skips counting them in its calculation of workdays.

Let's revisit the data in our first example and compare the outputs between the two functions. The formula we will use for the NETWORKDAY function is:

The output changes to 9 from 8. This is because out of the 12 days between December 25 and January 21, there are:

• 2 Thursdays (December 27 and January 3) and 2 Fridays (December 28 and January 4)
• 2 Saturdays (December 29 and January 5) and 1 Sunday (December 30)

Instead of 4, the NETWORKDAYS function only reduces the 3 off days as per the conventional Saturday-Sunday weekend.

That wraps up our tutorial on the NETWORKDAYS.INTL function. It's a function that is much more robust than the NETWORKDAYS function. With a ton of applications, it should certainly be on your priority list as you work your way to becoming an Excel champion. While you work on this function, we'll put together another tutorial for you. We won't run out — we promise.