Fabulous Examples of Excel Functions:
The NETWORKDAYS Function

Microsoft Excel provides many built-in functions. This tutorial discusses in detail the NETWORKDAYS function ("net workdays") and provides spreadsheet examples.

This tutorial a;so discusses how Excel stores dates, how to find a date's serial number, and how the NETWORKDAYS function works with constant arrays.

If you like video-based introduction, check out Excel 2010 Tutorial for Beginners. We have this course and find it very well done. An Advanced Excel course is also available, along with a lot of free video lessons online.

Author: Keynote Support

Before we begin, don't forget that every Excel function begins with an equal = sign unless the function is imbedded inside of another function. Click on a link to go directly to that topic.

Excel Stores Dates as Serial Numbers

Before discussing the NETWORKDAYS function, it is necessary that we understand how Excel stores dates. In order to allow calculations to be performed on dates, Excel stores dates as sequential serial numbers - beginning with 1 - which is January 1, 1900 on a Windows PC; and January 1, 2004 on a Macintosh.

Because Excel stores each date as a serial number, dates can be added, subtracted, and included in other calculations. A date's serial number can be seen by reformatting the cell containing the date to "Number" via the "Format Cells" window.

Please see How Microsoft Excel Handles Date and Time for more information about date serial numbers, Excel defaults, and formatting cells for date and time.

The NETWORKDAYS Function

The NETWORKDAYS function returns a count of the number of whole working days in a specified period of time. You may optionally request that Excel exclude certain days - holidays - from the count. The syntax of the NETWORKDAYS function is:

NETWORKDAYS(Start-date,End-date,Holidays)

  • Start-date is the serial number of the date from which to begin the count
  • End-date is the serial number of the last day to count
  • Holidays (optional) are the serial numbers of one or more normal working dates that Excel should exclude from the count. Holidays are discussed in-depth later in this tutorial

As mentioned above, Excel always stores dates as serial numbers. If you know the dates' serial numbers, entering the function is quick and easy. Look at the example below: 40665 is the serial number for 2-May-2011 and 40816 is the serial number for 30-Sep-2011:

=NETWORKDAYS(40665,40816)  This Excel function counts the number of days between 2-May-2011 and 30-Sep-2011 and returns a value of 110.

Since most folks don't know the dates' serial numbers, they will enter the dates into worksheet cells and then reference the cells in the function. Study the examples in the following worksheet and then read the descriptions that follow. The Start-date, End-date, and 3 holidays are in column B. The actual functions are in column D, with their text shown in column C.

image of Excel worksheet with the functions discussed below

=NETWORKDAYS(B2,B3)  This Excel function counts the number of days between 2-May-2011 (cell B2) and 30-Sep-2011 (cell B3) and returns a value of 110 (cell D2).

=NETWORKDAYS(B2,B3,B4)  This Excel function counts the number of days between 2-May-2011 and 30-Sep-2011, subtracts the holiday in cell B4, and displays a value of 109 (cell D3).

=NETWORKDAYS(B2,B3,B4:B6)  This Excel function counts the number of days between 2-May-2011 and 30-Sep-2011, subtracts 3 holidays (B4, B5, and B6), and displays a value of 107.

Determining Date Serial Numbers Quickly

If you don't want dates cluttering up your worksheet, there are two ways to determine dates' serial numbers.

Method 1: Find an unused cell and format it to display a Number with no decimal places. Type a date into the cell, press enter, and the date's serial number displays. After entering the serial number into the function, type the next date, and so on.

Method 2: Use the DATEVALUE function (discussed in The DATEVALUE Function). Type =DATEVALUE("mm/dd/yyyy") in a cell that has not been preformatted. The date must be enclosed in double quotation marks. Any date format that your version of Excel supports may be used. For example, both =DATEVALUE("5-2-2011") and =DATEVALUE("2-May-2011") return a value of 40665 when entered into a cell.

Regardless of the method, when you have all of the serial numbers that you need, the cells containing the dates can be erased if desired.

Specifying Holidays

There are 3 ways that holidays can be specified in the NETWORKDAYS function:

  • A single date — see the first example above where we excluded the holiday in cell B4
  • multiple dates — see the second example above. Multiple holidays are listed separately in contiguous cells and referenced as a range (B4:B6)
  • An array constant of date serial numbers — the holiday serial numbers are listed in an array constant

What if we accidently enter a serial number for a holiday that is not in the Start—End date range? Excel will ignore it. If we enter a serial number for a weekend date, Excel will ignore that as well since weekend dates are already excluded.

Using Array Constants for Holidays

An array constant is enclosed in brackets or curly braces { }, and the dates' serial numbers are separated by semi-colons or commas. The following function contains an array constant:

=NETWORKDAYS(B2,B3,{40707;40735})  This Excel function excludes from the count two holidays with serial numbers 40707 and 40735.

Converting Cell References to an Array Constant

If you have several weeks of holidays to exclude, here is a neat trick. Enter the Monday dates into the worksheet, and then reference these cells as holidays in the function. Then have Excel convert the cell references to an array constant, edit the function, and add the serial numbers of the other days of each week. Since serial numbers are sequential, you know what they are!

Using the worksheet below, see the steps to follow to use this technique:

image of Excel worksheet with the functions discussed below

In the worksheet above, we are converting the holiday cell references in the second example, B4 through B6. The result of our conversion is displayed in the next row. Follow these steps to convert the holidays to serial numbers.

  1. Select the cell containing the function and click on the function in the Formula Bar. The function's syntax displays (see image).
  2. Click on (holidays) in the function syntax and press the F9 key. (Cell references change to serial numbers).
  3. Press the Enter key.

Now edit the function and add as many serial numbers as you want! In cell D5 (and shown in C5), we added the remaining days of the week of July 11th.

Using the Insert Function Dialog Box

Newer versions of Excel provide the Insert Function facility to assist in entering functions. This facility is accessed by clicking the function icon - ƒx - located to the left of the formula bar.

To use this facility:

  • Click in the cell which will hold the function, click ƒx, and the "Insert Function" window displays
  • Type NETWORKDAYS in the top field and press Enter or click "Go"
  • NETWORKDAYS will be highlighted in the "Select a function" box. Click "OK" and the "Function Arguments" window displays
  • Enter the cell references containing the dates or date serial numbers in the spaces indicated, and click "OK" when finished

image of Excel worksheet with the functions discussed below


We hope this article has been helpful. Cheers!

Return to the top

Please link to this tutorial using the following HTML:

<a href="http://www.keynotesupport.com/excel-functions/networkdays-function.shtml">Fabulous Examples of Excel Functions: The NETWORKDAYS Function</a>