Excel NETWORKDAYS Function – How To Use

The NETWORKDAYS function is a Date/Time function in Excel that computes the number of days between two given dates. The function automatically omits weekends while computing the number of workdays. If you want to omit any non-weekend holidays, the NETWORKDAYS function can accommodate that, too.

Financial analysts use the NETWORKDAYS function to calculate the employee benefits that are a factor of the number of days worked. Businesses may use the NETWORKDAYS function to compute working days for collection from debtors, resolving customer support issues, and more.

Excel NETWORKDAYS Function

Syntax

The syntax of the NETWORKDAYS function is as follows:

=NETWORKDAYS(start_date, end_date, [holidays])

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.
'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 containing the list of holidays as an input in this argument.

Important Characteristics of the NETWORKDAYS function

  • By default, the NETWORKDAYS function assumes the weekend as Saturday and Sunday. They are automatically omitted while making the computation.
  • The NETWORKDAYS function allows you to add a list of non-weekend holidays using the holidays argument
  • The NETWORKDAYS function ignores time values and calculates the difference as full
  • 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 a date supplied in any argument is invalid, the function returns a #VALUE! error.

Examples

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

Example 1 – Plain Vanilla Formula for the NETWORKDAYS Function

Let's begin with the most basic use of the NETWORKDAYS function. We'll supply two dates to the function to compute the number of working days between them. We'll use the following formula:

=NETWORKDAYS(A2, B2)

Excel-NETWORKDAYS-Function-Example-01

The difference in terms of days between the start_date and the end_date is 12 days. However, the function returns 9 since December 29 (Saturday) and 30 (Sunday) is a weekend, and so is January 5 (Saturday). Let's shift to the second gear and input a list of holidays to this data.

Example 2 – Adding Holidays to the NETWORKDAYS Function

Let's use the data from the previous example and add a list of holidays to that. For instance, we have three holidays between these dates: December 25, December 31, and January 1. Assume that we have a list of holidays that includes these three dates in the cell range G2:G4.

In this case, we may calculate the net workdays using the following formula:

=NETWORKDAYS(A2, B2, G2:G4)

Excel-NETWORKDAYS-Function-Example-02

Since our holidays' list contains 3 days, the net workdays should reduce by 3. Note how the output changes to 6 from 9 after we add the list of holidays to the [holidays] argument.

These functions are really handy for understanding how many days employees are putting in. Although, what if you wanted to understand how much time factory workers are putting in? Let's remedy that.

Example 3 – Compute Hours Between Dates

Wage-earners work on a per-hour basis. Rather than offering benefits to a wage earner based on the number of workdays, it makes sense to use total hours worked. We don't need to get too "math-y" here. All we need to know to accomplish this is elementary-level math.

We'll use the following formula (assuming 7 hours per workday):

=NETWORKDAYS(A2,B2,G2:G4) * 7

Excel-NETWORKDAYS-Function-Example-03

Essentially, we're first calculating the number of net workdays, just as we did before, and multiplying them with the number of hours worked per workday. This, of course, gives us the number of hours worked between the two dates we've entered in the first two arguments.

Our output, of course, is 42 hours (6 days * 7 hours).

It's finally time to add some bulk to our formulas.

Example 4 – Calculating Workdays in a Month

Let's think about this for a moment. If we wanted to calculate the number of net workdays in a month, what should be our start_date and end_date? It doesn't take Einstein to figure out that we need to use the first and last dates of a given month.

However, what if you have a long list of the first days of each month? Instead of manually adding the last dates (or if you don't remember which months have 30/31 days), we could just use the EOMONTH function. The EOMONTH function must be nested in the end_date argument like so:

=NETWORKDAYS(A2, EOMONTH(A2,0), F2:F4)

Excel-NETWORKDAYS-Function-Example-04

The output is 21 days. Let's walk through how this magical number came about.

January has 31 days and 3 of them are holidays. However, note that one of the holidays (January 26, 2002) is also a Saturday, which the calculation omits anyway. Effectively, we're left with two deductible holidays.

Plus, there are 4 Saturdays (January 5, 12, 19, and 26) and 4 Sundays (January 6, 13, 20, and 27).

In total, we have 10 (2+4+4) non-workdays. Therefore, we have 21 (31 – 10) workdays in January. Why stop here, though? Let's look at how we can compute the total workdays in a year (we bet you already know).

Example 5 – Calculating Workdays in a Year

The logical construct of the formula we're about to use is identical to what we used in the previous formula. Put simply, we want the first day of the year as the start_date and the last day of the year as the end_date and we will be using the DATE function to construct those dates. Since the first and last days for all years are the same, we'll build those right into our formula. What we need, though, is a list of years.

This time around, we'll ignore the [holidays] argument to make understanding of the calculation easier. Once you have your list of years populated into a column, use the following formula:

=NETWORKDAYS(DATE(A2,1,1),DATE(A2,12,31))

Excel-NETWORKDAYS-Function-Example-05

This formula returns 261 for the year 2002. The year 2002 has 365 days (since it's not a leap year). Each year has 52 weeks (and therefore, 52 weekends). Effectively, this means 104 (52 * 2) days are Saturdays and Sundays. Removing 104 from 365 gives us 261. You could, of course, add a list of holidays too.

WORKDAY vs NETWORKDAYS

Both functions, though similar in connotation, have different purposes. The WORKDAY function calculates N working days into the future or past from a given date. On the other hand, the NETWORKDAYS function calculates the difference between two given dates.

For instance, say you want to calculate 15 working days into the future from December 25, 2001. We'll use the same list of holidays we used in the Example 2. We'll use the WORKDAY function like so:

=WORKDAY(A2, B2, G2:G4)

Excel-NETWORKDAYS-Function-VS-WORKDAYS-Function-06

The function returns January 17, 2002. The function omitted weekends and holidays (December 25, 29, 30, 31, and January 1, 6, 7, 13, 14) while calculating 15 working days into the future. The difference in the number of days between December 25, 2001 and January 17, 2002 is 24, but when we remove the weekends and holidays (9 days), we're left with 15 days.

See how the WORKDAY function differs from the NETWORKDAYS function.

Terrific, let's keep going.

NETWORKDAYS vs NETWORKDAYS.INTL

The NETWORKDAYS.INTL is a more versatile version of the NETWORKDAYS function. In essence, it accomplishes the same thing as the NETWORKDAYS function, plus a little more. Let's look at how.

The NETWORKDAYS.INTL function allows you to control the days that are treated as weekends. For instance, if your company has a policy where you're given Thursdays and Fridays off instead of Saturdays and Sundays, you can't use the NETWORKDAYS function to calculate the net working days.

This is when NETWORKDAYS.INTL function slams the door open and enters the scene wearing a Superman cape.

…alright, I'll dial it down.

Basically, you can use a set of predefined codes that tell Excel which two days of a week you want to treat as a weekend. Following are the codes:

CodeWeekend Days
1 (default)Saturday, Sunday
2Sunday, Monday
3Monday, Tuesday
4Tuesday, Wednesday
5Wednesday, Thursday
6Thursday, Friday
7Friday, Saturday
11Sunday
12Monday
13Tuesday
14Wednesday
15Thursday
16Friday
17Saturday

Let's continue with the data we've used in Example 2 and see how we can use the NETWORKDAYS.INTL function on that data, assuming a Thursday and Friday weekend. We'll use the following formula:

=NETWORKDAYS.INTL(A2, B2, 6, G2:G4)

Excel-NETWORKDAYS-Function-VS-NETWORKDAYS.INTL-Function-07

The NETWORKDAYS.INTL function has four arguments. The third argument is where we can set the weekend days from. The rest of the arguments are the same as for the NETWORKDAYS function.

Our output now changes to 5. There are 12 days between December 25, 2001 and January 5, 2002. 3 of them are holidays and 4 days are Thursdays/Fridays (December 27, 28, and January 3, 4). Therefore, our final output is 5 (12 – 9) days.

Alright Excel ninjas, that brings us to the end of the NETWORKDAYS function tutorial. It's been quite a ride, so pat yourself on the back and grab a cup of coffee before you resume your practice.

When you're done with the NETWORKDAYS function, pick up the NETWORKDAYS.INTL function to see what else you can do with the dates on your Excel sheets.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.