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.)
Search JabSto ::




Custom Search