You are currently offline, waiting for your internet to reconnect

HOW TO: Use NETWORKDAYS with Multiple Holidays in Excel 2000

This article was previously published under Q259200
For a Microsoft Excel 5.0 and earlier version of this article, see 89507.

IN THIS TASK

SUMMARY
This step-by-step article describes how to use the NETWORKDAYS function with multiple holidays in Microsoft Excel 2000.

When you use the NETWORKDAYS function with multiple holidays, you can enter the holidays as cell references or in the serial numberformat, and you must enclose the dates in braces ({}).

In the NETWORKDAYS function, dates in the form of text are ignoredif your worksheet is in the 1904 date system. Dates represented as serial numbers are accepted.

back to the top

NETWORKDAYS

The NETWORKDAYS function returns the number of whole working daysbetween two dates. You can use the holidays parameter to exclude specificdates from the calculation. You can enter starting and ending dates directly as serial numbers, as text, through a cell reference, or byusing the DATEVALUE function. You can also enter single holidays this way. However, you must enter multiple holidays in the serialnumber format only, and you must enclose the set of holiday dates inbraces ({}).

NOTE: If the NETWORKDAYS function returns a #NAME error, follow these steps:
  1. On the Tools menu, click Add-Ins.
  2. Click to select the Analysis ToolPak check box. Click OK.
  3. Click Yes if you receive an installation or update message.
  4. After the installation or update, click outside the cell that contains the formula, click to select the cell again, and then press ENTER.
back to the top

DATEVALUE

The correct way to indicate dates in the form of text is to use theDATEVALUE function. Also, if you are using an array constant tospecify multiple "text" dates for the holidays argument, you mustenclose the array constant within the DATEVALUE function. An exampleis provided in the Microsoft Excel Help file.

back to the top

Examples That List Dates in the Function

=NETWORKDAYS(DATEVALUE("1/1/92"),DATEVALUE("12/31/92"), DATEVALUE({"12/24/92","12/25/92"}))

-or-

=NETWORKDAYS(DATEVALUE("1/1/92"),DATEVALUE("12/31/92"),{"12/24/92","12/25/92"})

-or-

=NETWORKDAYS(DATEVALUE("1/1/92"),DATEVALUE("12/31/92"),{33962,33963})
back to the top

Examples That Use Cell References

A1: 12/24/92
A2: 12/25/92

Then, type the following formula in cell D1:

=NETWORKDAYS(DATEVALUE("1/1/92"),DATEVALUE("12/31/92"),A1:A2)

-or-

A1: 1/1/92
A2: 12/31/92
A3: 12/24/92
A4: 12/25/92

Then, type the following formula in cell D1:

=NETWORKDAYS(A1,A2,A3:A4)
Both of the examples above give 260 as the result.

You cannot, however use the following:
A1: 12/24/92
C1: 12/25/92

Then, type the following formula in cell D1:

=NETWORKDAYS(DATEVALUE("1/1/92"),DATEVALUE("12/31/92"),{A1,C1})
You receive a reference error because the cell references do not translate into date serial numbers.

back to the top
REFERENCES
For more information about the NETWORKDAYS function, click Microsoft Excel Help on the Help menu, type networkdays worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

back to the top
XL2000
Properties

Article ID: 259200 - Last Review: 09/25/2003 13:36:00 - Revision: 3.0

  • Microsoft Excel 2000 Standard Edition
  • kbhowtomaster KB259200
Feedback