Microsoft Office Tutorials and References

In Depth Information

**Date-Related Functions**

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.

This workbook,
work days.xlsx
, is available on the companion 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 8 and the project requires ten working days to complete, the

WORKDAY function can calculate the date that you will finish the project.

The following formula uses the WORKDAY function to determine the date ten working days from

January 8, 2010. A
working day
is a weekday (Monday through Friday).

=WORKDAY(“1/8/2010”,10)

The formula returns a date serial number, which must be formatted as a date. The result is

January 22, 2010 (four weekend dates fall between January 8 and January 22).

The preceding formula may return a different result, depending on your regional date

setting. (The hard-coded date may be interpreted as August 1, 2010.) A better formula is

=WORKDAY(DATE(2010,1,8),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).

Calculating the number of years between two dates

The following formula calculates the number of years between two dates. This formula assumes

that cells A1 and B1 both contain dates:

=YEAR(A1)-YEAR(B1)

This formula uses the YEAR function to extract the year from each date and then subtracts one

year from the other. If cell B1 contains a more recent date than the date in cell A1, then the result

is negative.