getting age date birth

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Getting Age from Date of Birth Formula

Getting Age from Date of Birth Formula

resolvedResolved · Low Priority · Version Standard

James has attended:
Excel Intermediate course

Getting Age from Date of Birth Formula

I have exported some data to excel, which has the candidate date fo birth, I know there is a formula within excel allowing it calculate the age for all those candidates....Does anyone know how to do it?
Fingers crossed
Thanks in advance.....a Swift response would be appreciated.

RE: Getting Age from Date of Birth Formula

Hi James

Thank you for your question

There is a little known function in Excel called Datediff() and it calculates the difference between 2 dates.

The syntax would be as follows

=DATEDIF(F9,TODAY(),"y")

F9 is the cell containing the person's date of birth
Today() is a function that returns today's date
"y" specified that the difference be measured in years.( "m" would measure it in months etc.)


Hope this helps

Regards

Stephen

RE: Getting Age from Date of Birth Formula

Stephen

I have 2 colums one with DOB and other with todays date the forumla you have given me does not seem to work....the 'y' seems to be an issue..should y have a set value?

Let me know

tHanks

RE: Getting Age from Date of Birth Formula

The 'y' (third parameter) indicates that datediff returns the result in years. So if that's what you want, you should keep it as y.

Try copying Stephen's formula from above and just change the first parameter to the cell reference of the DOB you want to calculate.

Regards, Rich

RE: Getting Age from Date of Birth Formula

I am now using the new excel 2007 instead of 2003 version and the formula does not appear to work, has the formula changed with the new 2007 version?

RE: Getting Age from Date of Birth Formula

Dont worry I have figured it out!


 

Excel tip:

Stop Formula Returning A "#DIV/0" Error

If a formula returns a #DIV/0 error message there is a way to avoid such results.

For example the formula =A1/B1 will return a #DIV/0 if B1 is empty or a zero.

If you protect your formulas with the ISERROR function, the formula will then look like this:

=IF(ISERROR(A1/B1),0,A1/B1)

In plain English: should the result of A1 divided by B1 be an error change the result to 0 else show the result of A1/B1.

View all Excel hints and tips


Server loaded in 0.09 secs.