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

**Method 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:

*=INT((B2-A2)/365)*

**Method 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.

**Method 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.

**Method 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.

## Comments

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.

#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").

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,"")

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.

=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

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!

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")

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!

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

Thanks,

Sandeep Sanjeevi.

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..=(DATEDIF(A1,TODAY(),"Y")& " Years, " &DATEDIF(A1,TODAY(),"YM")& " Months, " &DATEDIF(A1,TODAY(),"MD")& " Days)

Thanks