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

Custom Search