help to convert date of birth to age in Excel 2010

**rahvar77** · 02-21-2011, 07:23 AM

Hi dear friend
first sorry for my bad English
I collect information of about 18000 pationts to analysis. for my study i need age of them. but I have only birth date of them. I want "automatically" not one bye one convert that dates to age for each patient. by bellow formula i can do that one bye one!

=ROUNDDOWN(YEARFRAC(B2, TODAY(), 1), 0)
I think B2 should automatically change to B3,B4,... to B18000

PLEASE HELP ME
THANKS

**Marcol** · 02-21-2011, 07:59 AM

Try this

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

Drag/Fill Down

Hope this helps

[EDIT]
Use the handle (small square bottom right of cell) to drag the formula down.
This will increment the cells automatically.

Your formula seems to work okay.

Is that what you mean by automatic?

**daddylonglegs** · 02-21-2011, 08:47 AM

DATEDIF is better to get an accurate age as most people would understand it, i.e. it increments by 1 each birthday, using ROUNDDOWN with YEARFRAC can give inaccurate results on the birthday itself, e.g. in this formula

=ROUNDDOWN(YEARFRAC(DATE(1992,6,20),DATE(2011,6,20), 1), 0)

the two dates are exactly 19 years apart but the formula returns a result of 18

**MattMatt** · 04-21-2011, 08:04 AM

A method I use to show the current age, calculated from DoB is as follows (a bit lengthy, but works!)...

=DATEDIF($G2,NOW(),"y") & "y,"&DATEDIF($G2,NOW(),"ym") & "m,"& DATEDIF($G2,NOW(),"md") & "d"

This shows the age as (for example) 54y,3m,21d when the DoB is in column G. Again, the formula can be filled-down to calculate for a sheet of data.

Hope it helps!

**LAHLOULAH** · 01-03-2012, 02:54 PM

Originally Posted by Marcol

Try this

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

Drag/Fill Down

Hope this helps

[EDIT]
Use the handle (small square bottom right of cell) to drag the formula down.
This will increment the cells automatically.

Your formula seems to work okay.

Is that what you mean by automatic?

THANK YOU MARCO!!! This really helped me, you are the best