In this video I demonstrate how to calculate a person’s current age as well as how to calculate their age on a specific date. Download the featured file here.
Calculate a Person’s Current Age
The formula to calculate a person’s current age is:
=YEARFRAC(B2, TODAY()), where B2 is the cell that contains that person’s date of birth. You can obviously store the DOB in any cell you want.
The two functions used in this formula are the TODAY function and the YEARFRAC function. The TODAY function returns the current date and keeps it up-to-date whenever the worksheet is refreshed. The YEARFRAC function calculates the number of years between a start date and an end date.
If you want to remove any decimal places from the YEARFRAC calculation, you will also need to round down the answer. To do this use the INT function, as shown below.
Calculate a Person’s Age on a Specific Date
You can also use the YEARFRAC function to calculate a person’s age on a specific date. In the example above we used the TODAY() function to return the end date. Instead of doing this, refer to a cell that contains the specific date on which you are basing your calculation.
Calculate a Person’s Age in Years and Months
To calculate a person’s age in years and months use the DATEDIF function. Here’s how it would look (where B2 is the cell containing the person’s DOB):
=DATEDIF(B2,TODAY(),”Y”)&” Yrs “& DATEDIF(B2,TODAY(),”YM”)&” Mths”
As you can see you need to use two DATEDIF calculations in this formula: one for the year calculation and one for the month calculation. The & characters join the results of the DATEDIF calculations in order to make one text string, which would look something like this: 20 Yrs 7 Mths