Page 277 - Excel 2007 Bible

18_044039 ch13.qxp 11/21/06 11:05 AM Page 234
Part II
Working with Formulas and Functions
Excel automatically formats this formula cell as a date rather than as a numeric value. Therefore, you will
need to change the number format so that the result is displayed as a nondate. If cell B1 contains a more
recent date than the date in cell A1, the result will be negative.
If this formula does not display the correct value, make sure that A1 and B1 both contain
NOTE
NOTE
actual dates — not text that looks like a date.
Sometimes, calculating the difference between two days is more difficult. To demonstrate, consider the com-
mon 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 num-
ber 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, 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 (assuming that 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
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
NEW FEATURE
NEW FEATURE Analysis ToolPak add-in was installed. The function is now part of Excel 2007.
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 13.4 shows a worksheet that calculates the work days between two dates. The range A2:A11 con-
tains a list of holiday dates. The two formulas in column C calculate the work days between the dates in
column A and column B. For example, the formula in cell C15 is
=NETWORKDAYS(A15,B15,A2:A11)
This formula returns 4, which means that the seven-day period beginning with January 1 contains four
work days. In other words, the calculation excludes one holiday, one Saturday, and one Sunday. The for-
mula in cell C16 calculates the total number of work days in the year.
This workbook is available on the companion CD-ROM. The file is named work days.xlsx.
ON the CD-ROM
ON the CD-ROM
Offsetting a date using only work days
The WORKDAY function is the opposite of the NETWORKDAYS function. For example, if you start a project
on January 4, and the project requires ten working days to complete, the WORKDAY function can calculate
the date you will finish the project.
234