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.

Hot
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.

shot-convert-birthdate-0

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:

=INT((B2-A2)/365)


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.

shot-convert-birthdate-2


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.

shot-convert-birthdate-1


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"

shot-convert-birthdate-3

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

Comments  

+2#Berjang Pun2013-11-29 16:38
:lol: it is helpful for me
Reply | Reply with quote | Quote
+2#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
0#she2014-08-07 05:46
Quoting Thom H:
=FLOOR() would also do the trick

how is this being used? () what should be inside this parentheses?
Reply | Reply with quote | Quote
+2#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
+3#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
-1#KASSEM2014-05-01 12:32
hi i tried all of those methods and it didnt work out it alwyas return with result (#value!)
Reply | Reply with quote | Quote
0#Thom H2014-05-03 13:39
Hi Kassem,

#Value means that the Cells your Function refers to are not in the correct format for the Formula you are using i.e. using =SUM() on two Cells containing Text. All of the above functions are written assuming we're working with Cells containing only Dates.

The trouble is, Excel can sometimes see something as a different format to that which you see. Firstly, make sure the dates are in the correct uniform (DD/MM/YYYY). Secondly, make a small amendment to the Cell references in the Date Formula using the =text() function to tell it to read it as a Date .

Assuming we're using the DATEDIF() function with the Birthdate in A1 and the current date in B1, which would be =DATEDIF(A1,B1,"y"), we can force the Function to read Cell A1 as a date by amending it like so =DATEDIF(TEXT(A1,"DD/MM/YYYY"),B1,"y").
Reply | Reply with quote | Quote
+1#Daini Malhotra2014-05-04 06:38
i like method d for calculating age, it's very easy & suitable formula in excel.
:D
Reply | Reply with quote | Quote
+1#Daini Malhotra2014-05-04 06:40
=DATEDIF(A13,TODAY(),"Y") & " Years, " & DATEDIF(A13,TODAY(),"YM") & " Months, " & DATEDIF(A13,TODAY(),"MD") & " Days" this formula is very easy & suitable for calculating age in excel, i like it..............
Reply | Reply with quote | Quote
0#mark042014-05-07 14:33
method D is returning a value while date cell is blank, how to return value "0" or blank when there's no date filled in cell?
Reply | Reply with quote | Quote
0#Thom H2014-05-08 06:45
Hi Mark

Excel uses an internal calendar which starts at 01/01/1900. When a start date isn't present it will automatically choose the first date in its calendari I'd say use an IF() function, assuming the start date is in cell A1, you could use =IF(A1="","",INSERT CHOSEN DATE FUNCTION HERE)

This function checks cell A1, then returns no value if the cell is blank, but does your chosen formula if the cell is active (contains data).

If it's likely that sometimes the start date will be blank and sometimes the end date will, then you'll need to add in the AND( ) function so once again assuming start date is in Cell A1 and end is in B1 you'd use =IF(AND(A1>0,B1>0),INSERT CHOSEN DATE FUNCTION HERE,"")
Reply | Reply with quote | Quote
0#Thom H2014-05-08 21:22
Hi Mark04,

Excel will automatically treat the Empty Cell as a date and just use the first date in it's Calendar (01/01/1900). You'll need to use an IF() function.

Assuming the start date is in A1 and the end date is in B1 I'd go for =IF(AND(A1>0,B1>0),INSERT CHOSEN DATE FUNCTION HERE,"")
This will leave the Cell Blank if there isn't a value in both Cells A1 & B1.
Reply | Reply with quote | Quote
0#Meak Kanann2014-05-09 06:46
I really love this website. I can find whatever I need immediately. Plz continue to update n search for more useful function. :P
Reply | Reply with quote | Quote
0#mallikarjuna swamy2014-05-10 02:23
04/05/2012
=DATEDIF(A13,TODAY(),"Y") & " Years, " & DATEDIF(A13,TODAY(),"YM") & " Months, " & DATEDIF(A13,TODAY(),"MD") & " Days"



i WANT TO CALCULATE DATE AS OF TODAY BUT WHEN I INSERTED THE FORMULA IN THE CELL I AM GETTING

114 Years, 4 Months, 10 Days WHICH IS OBVIOUSLY WRONG
Reply | Reply with quote | Quote
0#Thom H2014-06-04 19:05
Is the first date definitely in Cell A13 as that's where the Argument is looking for the first date. It looks as though A13 is blank, thus it's counting the difference between today and the start of Excel's Calendar (What it would consider as date 0).

The only other thing it could be is that it's reading the first date as blank because it's not in the correct format, but I believe that would bring up a #Value error. Either way, it's worth checking the format of the Cell. You can either go right click> format Cells> Date> OK, or you can switch each reference to Cell A13 with an =TEXT() so it would be:
=DATEDIF(TEXT(A13,"DD/MM/YYYY"),TODAY(),"Y") & " Years, " & DATEDIF(TEXT(A13,"DD/MM/YYYY"),TODAY(),"YM") & " Months, " & DATEDIF(TEXT(A13,"DD/MM/YYYY"),TODAY(),"MD") & " Days"

Before all of that though, did you copy and paste your Function straight from your Spread sheet, because I error checked it and there were a couple of spaces in the TODAY() functions that threw it and gave me a #NAME error. Try it again by copying and pasting the following:

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

Once again, make sure your start date is in Cell A13!
Reply | Reply with quote | Quote
0#Dennison2014-05-13 13:42
Does Method D compensate for the day lost/gained on a leap year?
Reply | Reply with quote | Quote
0#Jodi2014-05-16 17:48
Hi, How do you use Index and Match function to find age from a person birth date? Thanks.
Reply | Reply with quote | Quote
0#VRB2014-06-03 20:04
i need a formula based on a child's birthdate how old he will be on September 1, 2014 (or any other year). cannot finagle the formula =DATEDIF(A13,TODAY(),"Y") to not be TODAY, but 9/1/14. can someone help. I'm sure it's easy :)
Reply | Reply with quote | Quote
0#Thom H2014-06-04 18:55
Hi VRB,

You could either put the date in a separate cell and reference that Cell instead of TODAY(). This would mean you can change the date as you please without having to risk breaking the Argument (Function). Assuming the Date (01/09/14) is in Cell A1, you could use =DATEDIF(A13,TEXT(A1,"DD/MM/YYYY"),"Y")

Alternatively, if you want to just switch the TODAY() function with that date then you'd use this: =DATEDIF(A13,DATE(2014,9,1),"Y")
Reply | Reply with quote | Quote
0#Lucy2014-06-04 04:46
Hi,
May I know why when I try get for the next age, its become error?

28/06/1986 04/06/2014 27
"28/03/1987 04/06/2014 #VALUE!
"03/03/1942 04/06/2014 #VALUE!
"01/01/2000 04/06/2014 #VALUE!
"03/03/1945 04/06/2014 #VALUE!
Reply | Reply with quote | Quote
0#Sandeep Sanjeevi2014-06-10 07:31
Hi,

This is very helpful and very clear to understand for any one.Helped me a lot.

Thanks,
Sandeep Sanjeevi.
Reply | Reply with quote | Quote
0#sanoj xavier2014-06-19 01:06
how do I convert 19610821 to age?
Reply | Reply with quote | Quote
+1#aamir khan2014-06-28 15:55
for example if date12/6/1983 i want to calculate the date month year till 1/1/2014 what will be the formula? please help me..
Reply | Reply with quote | Quote
0#hk2014-07-02 15:48
Hi, how would I incorportate a text field form into this formula(DOB bookmark referencing the DOB in this formula) into a MSWord 2010 document? I've also tried creating a table in MSWord with 2 cells (DOB in A1 and formula in A2) and I get a syntax error. Here is my formula (with and without the parentheses at the beginning and end gives me the same error). A1 cell contains "7/1/2010":

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

Thanks
Reply | Reply with quote | Quote
-1#satheesh2014-08-03 23:57
Why datedif formula not functioning in ms excel 2007?
Reply | Reply with quote | Quote
0#CI2014-08-15 16:21
Hi, If I use the formulas provide I would need the age to not advance even if they have a birthday tomorrow. For example if I use the formula today 08/15/2014 and they have a birthday of 08/16/2014 I need the spreadsheet to essentially lock or remember the age on 08/15/2014.

Thanks
Reply | Reply with quote | Quote

Add comment


Security code
Refresh