HOW TO: Use NETWORKDAYS with Multiple Holidays in Excel 2000

Article translations Article translations
Article ID: 259200 - View products that this article applies to.
This article was previously published under Q259200
Expand all | Collapse all

On This Page

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.

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.

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 "text" 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.

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})

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.

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.

Properties

Article ID: 259200 - Last Review: September 25, 2003 - Revision: 3.0
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
Keywords: 
kbhowtomaster KB259200

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com