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 diffi 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)
Search JabSto ::




Custom Search