How-To

How to Calculate Age From Date of Birth in Excel

Working in spreadsheets

If you have a person’s date of birth, you may want to work out how old they are. Learn how to calculate age from date of birth in Excel using this guide.

If you have a data set in Excel containing a number of dates of birth, you might want to know the current age of the people those dates belong to. Thankfully, Excel makes it simple to calculate the age of a person from their date of birth.

You have a number of options to do this. First, you can calculate age in years. If you want to be even more precise, you can calculate someone’s age in years, months, and days. It’s also possible to calculate how old someone will be at any age you specify.

If you want to know how to calculate age from date of birth in Excel, follow the steps below.

How to Calculate Current Age from Date of Birth in Excel

You can use a number of Excel functions to help you calculate a person’s age. The best function to use will depend on the format that you want to use to show the age.

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(
    excel yearfrac function
  4. Select the cell containing the date of birth.
    excel date of birth
  5. Type ,TODAY())) and press Enter.
    excel yearfrac formula
  6. The person’s age in years is calculated.
    excel yearfrac result
  7. 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.
    excel drag handle
  8. Drag down to apply the formula to other cells.
  9. Release the mouse—your other ages will calculate.
    excel ages

How to Calculate Current Age in Years and Months

The above method will give someone’s age in years based on their date of birth, but you might want to be more accurate than that. If so, you can use a different function to calculate someone’s 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(
    excel years and months formula
  3. Click the cell containing the date of birth.
    excel date of birth
  4. Type ,TODAY(),”Y”) & ” Years and ” & DATEDIF(
    excel years and months formula
  5. Click the cell containing the date of birth again.
    excel date of birth
  6. Type ,TODAY(),”YM”) & ” Months” and press Enter.
    excel years and months formula
  7. The age in years and months will be calculated.
    excel years and months results
  8. To apply the formula to other cells, click and hold the square in the bottom-right of the cell and drag it down.
    excel drag handle
  9. The other ages are now calculated.
    excel ages in years and months

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(
    excel years and months formula
  3. Click the cell containing the date of birth.
    excel date of birth
  4. Type ,TODAY(),”Y”) & ” Years and ” & DATEDIF(
    excel years and months formula
  5. Click the cell containing the date of birth again.
  6. Type ,TODAY(),”YM”) & ” Months and ” DATEDIF(
    excel years months days formula
  7. Click the cell containing the date of birth one more time.
  8. Type ,TODAY(),”MD”) & ” Days” and press Enter.
    excel full years months dates formula
  9. The age will calculate in years, months, and days.
    excel age in years months and days
  10. To apply the formula to other cells, click and hold the small square in the bottom-right of the cell and drag it downward.
    excel drag handle
  11. The other ages will calculate.
    excel ages in years months and days

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(
    excel specific date
  3. Click the cell containing the date of birth.
    excel date of birth
  4. Type a comma, then click the cell containing the specific date you want to use.
    excel end date
  5. Type two closed brackets and press Enter.
    excel specific date formula
  6. The age on the specified date will be calculated.
    excel age on specific date
  7. If you change the specified date, the age will automatically recalculate.
    excel specific date result

Take Advantage of the Power of Excel

Excel can be used for far more than just tables of data. There is a huge selection of functions that you can use to calculate almost anything you wish. Knowing how to calculate age from date of birth in Excel is just one example.

Using similar functions, you can use Excel to calculate years of service to help calculate bonus payments. You can use functions to remove the time from a date stamp in Excel. You can even get Excel to ring an alarm when your data hits specific values.

1 Comment

1 Comment

  1. Mark

    Doesn’t work for me except for years. Years and months I get #NAME, years month a days I get an error: There’s a problem with the formula. Excel 2019
    =DATEDIF(A3,TODAY(),”Y”) & ” Years and ” & DATEDIF(A3,TODAY(),”YM”) & ” Months and ” DATEDIF(A3,TODAY(),”MD”) & ” Days”

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top