How to Calculate Current Age from Date of Birth in Excel

 

How to Calculate Current Age in Full Years

If you want to calculate someone’s current age from their date of birth, the best choice is to use the YEARFRAC function. This returns the total number of completed years plus the fraction of the current year.

Since people usually give their age in completed years, we can truncate the result to remove the decimal part and just leave the number of years.

To calculate age in years using YEARFRAC:

1. Open Excel.
2. Click in the cell where you want the age to appear.
3. Type =TRUNC(YEARFRAC(
   
   
4. Select the cell containing the date of birth.
   
5. Type ,TODAY())) and press Enter.
   
6. The person’s age in years is calculated.
7. 
8. If you have other ages you need to calculate, click the cell containing your formula, then click and hold the small square in the bottom right-hand corner of the cell.
   
9. Drag down to apply the formula to other cells.
10. Release the mouse—your other ages will calculate.
    

How to Calculate Current Age in Years and Months

To calculate age in years and months in Excel:

1. Click in the cell where you want the age to appear.
2. Type =DATEDIF(
   
   
3. Click the cell containing the date of birth.
   
4. Type ,TODAY(),”Y”) & ” Years and ” & DATEDIF(
   
   
5. Click the cell containing the date of birth again.
   
6. Type ,TODAY(),”YM”) & ” Months” and press Enter.
   
7. The age in years and months will be calculated.
   
8. To apply the formula to other cells, click and hold the square in the bottom-right of the cell and drag it down.
   
9. The other ages are now calculated.
   

How to Calculate Current Age in Years, Months, and Days

If you want to be even more accurate, you can include years, months, and days. This uses the same DATEDIF formula three times to calculate each part separately.

To calculate age in years, months, and days in Excel:

1. Click in the cell where you want the age to appear.
2. Type =DATEDIF(
   
   
3. Click the cell containing the date of birth.
   
4. Type ,TODAY(),”Y”) & ” Years and ” & DATEDIF(
   
   
5. Click the cell containing the date of birth again.
6. Type ,TODAY(),”YM”) & ” Months and ” DATEDIF(
   
   
7. Click the cell containing the date of birth one more time.
8. Type ,TODAY(),”MD”) & ” Days” and press Enter.
   
9. Type ,TODAY(),”MD”) & ” Days” and press Enter.
   
10. The age will calculate in years, months, and days.
    
11. To apply the formula to other cells, click and hold the small square in the bottom-right of the cell and drag it downward.
    
12. The other ages will calculate.
    

How to Calculate Age on Specific Date in Excel

All of the methods above use the TODAY function to calculate the difference between the person’s date of birth and today’s date, giving their age today. It is possible to calculate someone’s age on any date of your choosing by replacing TODAY with the date you want to use.

To calculate age on a specific date in Excel:

1. Click in the cell where you want the age to appear.
2. Type =TRUNC(YEARFRAC(
   
   
3. Click the cell containing the date of birth.
   
   
4. Type a comma, then click the cell containing the specific date you want to use.
   
5. Type two closed brackets and press Enter.
   
6. The age on the specified date will be calculated.
   
7. If you change the specified date, the age will automatically recalculate.