Microsoft Office Tutorials and References
In Depth Information
Calculating a person’s age
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.
16
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))
In versions prior to Excel 2007, the YEARFRAC function was available only when the Analysis ToolPak add-in was
installed. The function is now part of Excel and does not require an add-in.
The following formula uses the DATEDIF function to calculate an age. (See the sidebar,
“Where’s the DATEDIF Function?”)
=DATEDIF(A1,TODAY(),"Y")
Where’s the DATEDIF Function?
One of Excel’s mysteries is the DATEDIF function. You may notice that this function doesn’t appear
in the drop-down function list for the Date & Time category, nor does it appear in the Insert Function
dialog box. Therefore, when you use this function, you must always enter it manually.
The DATEDIF function has its origins in Lotus 1-2-3, and apparently Excel provides it for compatibility
purposes. The function has been available since Excel 5, but Excel 2000 is the only version that ever
documented it in its Help system.
DATEDIF is a handy function that calculates the number of days, months, or years between two dates.
The function takes three arguments: start_date , end_date , and a code that represents the time
unit of interest. Here’s an example of a formula that uses the DATEDIF function (it assumes cells A1
and A2 contain a date). The formula returns the number of complete years between those two dates:
=DATEDIF(A1,A2,"y")
The following table displays valid codes for the third argument. (You must enclose the codes in
quotation marks.)
Continues
Search JabSto ::

Custom Search