How to convert birthdate to age quickly in Excel?

For instance, you get a range of various birthdate data in Excel, and you need to convert these birthdate to display their exact age value in Excel, how would you like to figure out? This article lists some tips to convert the birth date to age in Excel easily.

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

arrow blue right bubbleMethod A: Convert birthdate to age with subtraction

Normally we figure out someone's age with current date subtracting the given birth date. So does it in Excel.

Step 1: Enter current date in a blank cell, such as 2012/6/18 in Cell B2.

Step 2: In another blank cell, enter the formula =(B2-A2)/365, and press the Enter key.


Then it shows the age in the cell. However, you may get a strange age such as 23.70411. But the following function will figure out a normal age:


arrow blue right bubbleMethod B: Convert birthdate to age with DATEDIF function

Some would like to apply the DATEDIF function to calculate the age. Enter the formula =DATEDIF(A2,NOW(),"y") in a blank cell, it calculates the age immediately after pressing the Enter key.


arrow blue right bubbleMethod C: Convert birthdate to age with ROUNDDOWN function

Another function to convert the birth date to age is =ROUNDDOWN(YEARFRAC(A2, TODAY(), 1), 0), which will figure out a standard age, such as 23.


arrow blue right bubbleMethod D: Convert birthdate to exact age with DATEDIF function

Sometimes exact age is requires, and you may want to know how many years, months, and days from the birth date to current date. The following formula can help you figure out:

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


See the screen shot above, this formula will show you precise result, such as 23 Years, 8 Months, and 14 Days.

arrow blue right bubbleRelated Articles

Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase


+2#Berjang Pun2013-11-29 16:38
:lol: it is helpful for me
Reply | Reply with quote | Quote
+1#Robyn2013-12-13 20:51
I've been looking for the "round down" option, but didn't know there was one. Thank you!
Reply | Reply with quote | Quote
+1#Thom H2014-03-12 23:48
=FLOOR() would also do the trick
Reply | Reply with quote | Quote
+1#S Dorn2014-01-28 17:06
I need a formula that will allow me to punch in a DOB and then find out the exact day that a person turns exactly 70 1/2.
Reply | Reply with quote | Quote
+1#Thom H2014-03-12 23:47
=EDATE(A1,906) with DOB in Cell A1
Reply | Reply with quote | Quote
+1#Amit sharma2014-02-01 19:03
This is very helpful and very clear to understand for any one. I also need it. It help me a lot.
Reply | Reply with quote | Quote
+2#MICH2014-02-08 11:37
very nice..A13 means A2..Very helpful..makes work easier..thanks for the formula..
Reply | Reply with quote | Quote
+1#Roger2014-02-10 13:27
Try this formula to convert date of birht to Age.
Reply | Reply with quote | Quote
+1#kaushalendra2014-02-17 10:43
it is very helpful. It is helpful to beginners as well as skilled person
Reply | Reply with quote | Quote
+2#SDJohnson2014-02-26 23:33
I have been looking for a formula that gives me the age from a Bdate for months now, I found several but they never worked. This was so easy to understand...... Made me look for other items. :-) Thank you so much.
Reply | Reply with quote | Quote
+1#fawadkhan2014-03-01 11:26
hello this is fawad khan want some information about ageses
Reply | Reply with quote | Quote
+1#jayson2014-03-19 05:19
i have a whole bunch of cells i have to convert to age, how do i do it faster?
Reply | Reply with quote | Quote
0#Thom H2014-03-20 08:37
create the chosen formula in the cell to the right of the first DOB, then you can drag the function down all of the rows with the anchor in the bottom right of the cell containing the function.

This method will work for most of the above, but if you're using method a. i.e. One where the current date is a reference to a different cell that contains the current date, you'll need to amend the top formula to make it into an absolute reference, so if your formula was option one (=(B2-A2)/365) assuming your dates are all in column A, you can amend the too function to =($B$2-A2)/365. Notice how I've anchored the b2 cell reference while the a2 cell reference will change as you drag the function down.
Reply | Reply with quote | Quote

Add comment

Security code