Microsoft Office Tutorials and References
In Depth Information
To bring this analogy into the realm of dates, suppose you start a sales promotion on February 1 and end the
promotion on February 9. How many days was the promotion in effect? Subtracting February 1 from February
9 produces an answer of eight days. However, the promotion actually lasted nine days. In this case, the correct
answer involves counting the fence posts, as it were, and not the gaps. The formula to calculate the length of the
promotion (assuming you have appropriately named cells) appears like this:
Calculating the number of work days between two dates
When calculating the difference between two dates, you may want to exclude weekends and holidays. For ex-
ample, you may need to know how many business days fall in the month of November. This calculation should
exclude Saturdays, Sundays, and holidays. Using the NETWORKDAYS function can help.
The NETWORKDAYS function has a very misleading name. This function has nothing to
do with networks or networking. Rather, it calculates the net number of workdays
between two dates.
The NETWORKDAYS function calculates the difference between two dates, excluding weekend days
(Saturdays and Sundays). As an option, you can specify a range of cells that contain the dates of holidays,
which are also excluded. Excel has absolutely no way of determining which days are holidays, so you must
provide this information in a range.
Figure 6-4 shows a worksheet that calculates the workdays between two dates. The range A2:A11 contains a list
of holiday dates. The formulas in column C calculate the workdays between the dates in column A and column
B. For example, the formula in cell C15 is