Part II: Working with Formulas and Functions
264
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 formula in cell C16 calculates the total number of work days in the year.On the CD
This workbook is available on the companion CD-ROM. The file is named work days.xlsx.New
Excel 2010 includes an updated version of the NETWORKDAYS function, named NETWORKDAYS.INTL. This
new version is useful if you consider weekend days to be days other than Saturday and Sunday. n
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 10 working days to complete, the WORKDAY function
can calculate the date you will finish the project.Note
In versions prior to Excel 2007, the WORKDAY function was available only when the Analysis ToolPak add-in
was installed. The function is now part of Excel and doesn’t require an add-in. n
The following formula uses the WORKDAY function to determine the date that is ten working days
from January 4, 2010. A working day consists of a week day (Monday through Friday).=WORKDAY(“1/4/2010”,10)The formula returns a date serial number, which must be formatted as a date. The result is January
18, 2010 (four weekend dates fall between January 4 and January 18).Caution
The preceding formula may return a different result, depending on your regional date setting. (The hard-coded
date may be interpreted as April 1, 2010.) A better formula is=WORKDAY(DATE(2010,1,4),10)The second argument for the WORKDAY function can be negative. And, as with the NETWORKDAYS
function, the WORKDAY function accepts an optional third argument (a reference to a range that
contains a list of holiday dates).New
Excel 2010 includes an updated version of the WORKDAY function, named WORKDAY.INTL. This new version is
useful if you consider weekend days to be days other than Saturday and Sunday. n