Microsoft Office Tutorials and References
In Depth Information
Date-Related Functions
Note that this function doesn’t calculate full years. For example, if cell A1 contains 12/31/2010 and
cell B1 contains 01/01/2011, then the formula returns a difference of one year, even though the
dates differ by only one day.
You can also use the YEARFRAC function to calculate the number of years between two dates.
This function returns the number of years, including partial years. For example
=YEARFRAC(A1,B1,1)
Because the YEARFRAC function is often used for financial applications, it uses an optional third
argument that represents the “basis” for the year (for example, a 360-day year). A third
argument of 1 indicates an actual year.
Calculating a person’s age
A person’s age indicates the number of full years that the person has been alive. The formula in
the previous section (for calculating the number of years between two dates) won’t calculate this
value correctly. You can use two other formulas, however, to calculate a person’s age.
The following formula returns the age of the person whose date of birth you enter into cell A1.
This formula uses the YEARFRAC function:
=INT(YEARFRAC(TODAY(),A1,1))
The following formula uses the DATEDIF function to calculate an age. (See the sidebar, “Where’s
the DATEDIF function?”)
=DATEDIF(A1,TODAY(),”y”)
Determining the day of the year
January 1 is the first day of the year, and December 31 is the last day. But what about all of those
days in between? The following formula returns the day of the year for a date stored in cell A1:
=A1-DATE(YEAR(A1),1,0)
The day argument supplied is zero, calling for the “0th” day of the first month. The DATE
function interprets this as the day before the first day, or December 31 of the previous year in this
example. Similarly, negative numbers can be supplied for the day argument.

Search JabSto ::

Custom Search