Microsoft KB Archive/259200

= HOW TO: Use NETWORKDAYS with Multiple Holidays in Excel 2000 =

Article ID: 259200

Article Last Modified on 9/25/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



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
 * NETWORKDAYS
 * DATEVALUE
 * Examples That List Dates in the Function
 * Examples That Use Cell References

REFERENCES



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 number format, and you must enclose the dates in braces ({}).

In the NETWORKDAYS function, dates in the form of text are ignored if 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 days between two dates. You can use the holidays parameter to exclude specific dates from the calculation. You can enter starting and ending dates directly as serial numbers, as text, through a cell reference, or by using the DATEVALUE function. You can also enter single holidays this way. However, you must enter multiple holidays in the serial number format only, and you must enclose the set of holiday dates in braces ({}).

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 the DATEVALUE function. Also, if you are using an array constant to specify multiple &quot;text&quot; dates for the holidays argument, you must enclose the array constant within the DATEVALUE function. An example is provided in the Microsoft Excel Help file.

back to the top

Examples That List Dates in the Function
=NETWORKDAYS(DATEVALUE(&quot;1/1/92&quot;),DATEVALUE(&quot;12/31/92&quot;), DATEVALUE({&quot;12/24/92&quot;,&quot;12/25/92&quot;}))

-or-

=NETWORKDAYS(DATEVALUE(&quot;1/1/92&quot;),DATEVALUE(&quot;12/31/92&quot;),{&quot;12/24/92&quot;,&quot;12/25/92&quot;})

-or-

=NETWORKDAYS(DATEVALUE(&quot;1/1/92&quot;),DATEVALUE(&quot;12/31/92&quot;),{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(&quot;1/1/92&quot;),DATEVALUE(&quot;12/31/92&quot;),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(&quot;1/1/92&quot;),DATEVALUE(&quot;12/31/92&quot;),{A1,C1})

You receive a reference error because the cell references do not translate into date serial numbers.

back to the top

