You can use the following formulas to calculate the age of an individual on a specific date based on their date of birth:
Formula 1: Calculate Age in Years (e.g. 60 years)
=DATEDIF(A2, B2,"y")
Formula 2: Calculate Age in Decimal Years (e.g. 60.372 years)
=YEARFRAC(A2, B2)
Formula 3: Calculate Age in Years, Months, Days (e.g. 60 years, 4 months, 14 days)
=DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months, "&DATEDIF(A2,B2,"md")&" days"
Each formula assumes that the birth date is located in cell A2 and the specific date of interest is in cell B2.
The following examples show how to use each formula in practice.
Example 1: Calculate Age in Years
We can type the following formula into cell C2 to calculate the age of an individual in years on the date of 5/15/2040, assuming they were born on 1/1/1980:
=DATEDIF(A2, B2,"y")
The following screenshot shows how to use this formula in practice:
We can see that an individual who was born on 1/1/1980 will be 60 years old on 5/15/2040.
Example 2: Calculate Age in Decimal Years
We can type the following formula into cell C2 to calculate the age of an individual in decimal years on the date of 5/15/2040, assuming they were born on 1/1/1980:
=YEARFRAC(A2, B2)
The following screenshot shows how to use this formula in practice:
We can see that an individual who was born on 1/1/1980 will be 60.372 years old on 5/15/2040.
Example 3: Calculate Age in Years, Months, Days
We can type the following formula into cell C2 to calculate the age of an individual in years, months and days on the date of 5/15/2040, assuming they were born on 1/1/1980:
=DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months, "&DATEDIF(A2,B2,"md")&" days"
The following screenshot shows how to use this formula in practice:
We can see that an individual who was born on 1/1/1980 will be 60 years, 4 months and 14 days old on 5/15/2040.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate the Number of Months Between Dates in Excel
How to Convert Date to Month and Year Format in Excel
How to Calculate Average by Month in Excel
