Microsoft Office Tutorials and References
In Depth Information
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
Figure 6-3: Using the NETWORKDAYS function to calculate the number of working days between two
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.