Microsoft Office Tutorials and References

In Depth Information

**Calculating the number of workdays between two dates**

You can also use the
DAYS
worksheet function, introduced in Excel 2013. It offers no obvious advantage, but here’s

an example of how to use it to calculate the number of days between two dates:

=DAYS(A1,B1)

Sometimes, calculating the difference between two days is more difﬁ cult. To demonstrate,

consider the common fence-post analogy. If somebody asks you how many units make up a

fence, you can respond with either of two answers: the number of fence posts or the

number of gaps between the fence posts. The number of fence posts is always one more than the

number of gaps between the posts.

To bring this analogy into the realm of dates, suppose that 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. Actually,

though, the promotion lasted nine days. In this case, the correct answer involves counting

the fence posts, not the gaps. The formula to calculate the length of the promotion (assum-

ing that you have appropriately named cells) appears like this:

=EndDay-StartDay+1

Calculating the number of workdays 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. The

NETWORKDAYS
function can help out.

In versions prior to Excel 2007, the
NETWORKDAYS
function was available only when the Analysis ToolPak add-in

was installed. This function is now part of Excel and doesn’t require an add-in. This is relevant if you plan to share

your workbook with someone who uses an older version of Excel.

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 no way of determining which

days are holidays, so you must provide this information in a range.

Figure 16.3 shows a worksheet that calculates the workdays between two dates. The range

A2:A11 contains a list of holiday dates. The two 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)