Microsoft Office Tutorials and References

In Depth Information

**Date-Related Functions**

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 example, 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-3 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)

Figure 6-3:
Using the NETWORKDAYS function to calculate the number of working days between two

dates.

This formula returns 4, which means that the seven-day period beginning with January 1 contains

four workdays. In other words, the calculation excludes one holiday, one Saturday, and one

Sunday. The formula in cell C16 calculates the total number of workdays in the year.