Microsoft Office Tutorials and References

In Depth Information

**Calculating the number of years between two dates**

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.

The following formula uses the
WORKDAY
function to determine the date that is ten

working days from January 4, 2013. A workday consists of 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
function, 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, as described in an

earlier note.

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. (See the next section for another way to

calculate the number of full years.)