How-To

How to Calculate Age From Date of Birth in Excel

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 Using YEARFRAC

If you want to calculate someone’s current age from their date of birth, you need to know what is the formula for calculating their age. Quite often, 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, you might want to know how to calculate just the years from a date in Excel. In this case, we can truncate the result to remove the decimal part and just leave the number of years. To calculate the age from the date of birth in Excel using YEARFRAC, follow these steps.

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. 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.
8. Drag down to apply the formula to other cells.
9. Release the mouse — your other ages will calculate.

So, for my example above, where the dates are in column B, here would be my final Excel Formula:

TRUNC(YEARFRAC(B2,TODAY()))

How to Calculate Current Age in Years and Months in Excel

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, follow these steps.

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.

Again, for this example, to calculate age in Years and Months where the dates are in column B, the final formula would be:

=DATEDIF(B2,TODAY(),"Y") & " Years and " & DATEDIF(B2,TODAY(),"YM") & " Months"

Performing the Same Calculation Using DATEDIF

If you want to be even more accurate, you can include the 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 using DATEDIF, follow these steps.

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. The age will be calculated 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.
11. The other ages will be calculated.

Again, the final formula to calculate age in Years, Months, and Days where the dates are in column B is:

=DATEDIF(B2,TODAY(),"Y") & " Years and " & DATEDIF(B2,TODAY(),"YM") & " Months and " & DATEDIF(B2,TODAY(),"MD") & " Days"

How to Calculate Age on a Specific Date in Excel Using TODAY

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 the age on a specific date in Excel using the TODAY function, follow these steps.

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.

And finally, the last formula here to calculate age based on a date you provide would be:

=TRUNC(YEARFRAC(B2,C2))

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.

1. Mark

October 3, 2022 at 8:18 am

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”

• Rob

May 12, 2023 at 8:02 am

I am using Excel 2013 and I can only get the Years function to work. All others result in #NAME?

• Steve Krause

September 23, 2023 at 12:06 am

Hi Rob, to make it a bit easier, I updated the article to include the full formulas at the end of each step. Take a look. It should be working 100% if you just copy/paste the full formulas.

Thnx,

Steve

• Steve Krause

September 22, 2023 at 11:55 pm

Hi Mark,

I think there is a problem with the type of quotes you’re using.

If your dates are in A3, this formal will work for you:

=DATEDIF(A3,TODAY(),"Y") & " Years and " & DATEDIF(A3,TODAY(),"YM") & " Months and " & DATEDIF(A3,TODAY(),"MD") & " Days"
• Mark

September 25, 2023 at 8:27 am

Steve,
Thanks for that, it worked. I had to copy and paste the formula I tried and the one of yours in a text document to compare them to see where I went wrong. One ampersand and a space! Thanks Again!

• Steve Krause

September 25, 2023 at 7:11 pm

Excellent!

LOL — Yeah, I have no idea why we didn’t post the full completed formula when we first published the article. Thank you for the comment. It helped me realize just that!

Glad it worked out. It’s just crazy what Excel can do!

Steve
groovyPost.com

To Top