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:

=EndDay-StartDay+1

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

=NETWORKDAYS(A15,B15,A2:A11)