Note: The other languages of the website are Google-translated. Back to English

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.

Convert birthdate to age with formulas

The below formulas can help to calculate age based on birthdate in Excel.

Convert birthdate to age with the INT function

The INT function can help to calculate a person's age based on given birthdate, please do as follows.

1. Supposing there are two columns containing birthdate and current date separately.

2. Select a blank cell to output the age, enter the below formula into it and press the Enter key. Select the result cell and then drag it's Fill Handle down to get all results.

=INT((B2-A2)/365)

Convert birthdate to age with the DATEDIF function

The below DATEDIF function can also help.

Select a blank cell to output the age, enter the below formula into it and press the Enter key. Select the result cell and then drag it's Fill Handle down to get all results.

=DATEDIF(A2,NOW(),"y")

Convert birthdate to age with the ROUNDDOWN function

Try the ROUNDDOWN function to calculate age based on birthday as below.

Select a blank cell to output the age, enter the below formula into it and press the Enter key. Select the result cell and then drag it's Fill Handle down to get all results.

=ROUNDDOWN(YEARFRAC(A2, TODAY(), 1), 0)

Display the age as Year + Month + Day format with the DATEDIF function

If you want to display the age as Year + Month + Day format, please try the below DATEDIF function.

Select a blank cell to output the age, enter the below formula into it and press the Enter key. Select the result cell and then drag it's Fill Handle down to get all results.

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

Easily convert birthdate to age without remembering formulas

You can easily convert birthdate to age without remembering formulas with the Date & Time Helper of Kutools for Excel.

1. Select a blank cell to output the age. Then click Kutools > Formula Helper > Date & Time Helper

2. In the Date & Time Helper dialog box, please configure as follows.

• 2.1) Go to the Age tab;
• 2.2) In the Date of Birth box, select the cell containing the birth date you will convert to age;
• 2.3) Choose the Today option in the To section;
• 2.4) Specify an result type from the Output result type drop-down list;
• 2.5) Click the OK button.

Then the age is populated in selected cell. Select the result cell and then drag the Fill Handle all the way down to get all ages.

Note: If you want to display the age as Year + Month + Day format, please select Year + Month + Day from the Output result type drop-down list. And the result will be shown as the below screenshot shown. It also supports to display the age as month, week or day based on your needs.

If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
:lol: it is helpful for me
Berjang Pun
This comment was minimized by the moderator on the site
[quote]:lol: it is helpful for meBy Berjang Pun[/quote] VERY EASY TO KNOW THE DATE OF BIRT COUNT AS YEARLY AGE..? YOU CAN ..AS BELOW 10/8/1980-TODAY DATE=?????? THEN YOU CAN / BY 365 EASILY U WILL GETS THE POINT..
AMIT
This comment was minimized by the moderator on the site
I've been looking for the "round down" option, but didn't know there was one. Thank you!
Robyn
This comment was minimized by the moderator on the site
=FLOOR() would also do the trick
Thom H
This comment was minimized by the moderator on the site
[quote]=FLOOR() would also do the trickBy Thom H[/quote] how is this being used? () what should be inside this parentheses?
she
This comment was minimized by the moderator on the site
Hi Robyn, The Cell you wish to round down should be inside of the parentheses i.e. if you are rounding down cell A1 it would be =Floor(A1)
Thom H
This comment was minimized by the moderator on the site
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.
S Dorn
This comment was minimized by the moderator on the site
=EDATE(A1,906) with DOB in Cell A1
Thom H
This comment was minimized by the moderator on the site
This is very helpful and very clear to understand for any one. I also need it. It help me a lot.
Amit sharma
This comment was minimized by the moderator on the site
very nice..A13 means A2..Very helpful..makes work easier..thanks for the formula..
MICH
This comment was minimized by the moderator on the site
Try this formula to convert date of birht to Age.
Roger
This comment was minimized by the moderator on the site
it is very helpful. It is helpful to beginners as well as skilled person
kaushalendra
This comment was minimized by the moderator on the site
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.
SDJohnson
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
i have a whole bunch of cells i have to convert to age, how do i do it faster?
jayson
This comment was minimized by the moderator on the site
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.
Thom H
This comment was minimized by the moderator on the site
hi i tried all of those methods and it didnt work out it alwyas return with result (#value!)
KASSEM
This comment was minimized by the moderator on the site
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").
Thom H
This comment was minimized by the moderator on the site
i like method d for calculating age, it's very easy & suitable formula in excel. :D
Daini Malhotra
This comment was minimized by the moderator on the site
=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..............
Daini Malhotra
This comment was minimized by the moderator on the site
a problem i faced. like: birth date = 1st Nov.'2014, today is 31st Dec.'2015 then the result comes = 1 year, 1 month, 30 days, but the days should be 1day more i.e. 31 days. how can I do this in the above formula? pls help
Rasel
This comment was minimized by the moderator on the site
yes its very helpful for me. i easily understand. :)
Mubeen
This comment was minimized by the moderator on the site
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?
mark04
This comment was minimized by the moderator on the site
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,"")
Thom H
This comment was minimized by the moderator on the site
THOM H... thank you so much... you're the best!
JENNIEJEN
This comment was minimized by the moderator on the site
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.
Thom H
This comment was minimized by the moderator on the site
I really love this website. I can find whatever I need immediately. Plz continue to update n search for more useful function. :P
Meak Kanann
This comment was minimized by the moderator on the site
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
mallikarjuna swamy
This comment was minimized by the moderator on the site
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!
Thom H
This comment was minimized by the moderator on the site
Hi,,, you only looking this only ya,,, :-)
guru
This comment was minimized by the moderator on the site
Does Method D compensate for the day lost/gained on a leap year?
Dennison
This comment was minimized by the moderator on the site
Hi, How do you use Index and Match function to find age from a person birth date? Thanks.
Jodi
This comment was minimized by the moderator on the site
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 :)
VRB
This comment was minimized by the moderator on the site
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")
Thom H
This comment was minimized by the moderator on the site
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!
Lucy
This comment was minimized by the moderator on the site
Hi Lucy, I'd need more context as to what you're trying to set up, how it's done and your chosen function but it looks as though you've put the age function into the third column then you're dragging it down? If you check each function and just make sure the correct cells are referenced this should do the trick. #VALUE errors tend to mean that you're using referencing cells that don't contain data in the format the function expects. In this case the functions are looking for dates so Excel may well have converted your dates into text format? Easy fix = highlight the date cells, right click, format cells, format as date. If that doesn't work you'll need to create a secondary column next to each one (Which you can later hide) then use the =date() function to convert them into date format (see my reply to sanoj xavier further down the page for info on this function). Thanks, Thom
Thom H
This comment was minimized by the moderator on the site
Hi, This is very helpful and very clear to understand for any one.Helped me a lot. Thanks, Sandeep Sanjeevi.
Sandeep Sanjeevi
There are no comments posted here yet