Microsoft Office Tutorials and References

In Depth Information

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 4,

2013. A
working day
is a weekday (Monday through Friday).

=WORKDAY(“1/4/2013”,10)

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

(four weekend dates fall between January 4 and January 18).

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

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

=WORKDAY(DATE(2013,1,4),10)

The second argument for the WORKDAY function can be negative. And, as with the NETWORKDAYS func-

tion, the WORKDAY function accepts an optional third argument (a reference to a range that contains a list of

holiday dates).

Excel 2010 introduced an updated version of the WORKDAY function, named

WORKDAY.INTL. This newer version of the function is useful if you consider weekend

days to be days other than Saturday and Sunday.

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, the result is negative.

Note that this function doesn't calculate
full
years. For example, if cell A1 contains 12/31/2012, and cell B1

contains 01/01/2013, the formula returns a difference of one year even though the dates differ by only one day.