# Thread: Convert date-of-birth to age (Excel 2003)

1. ## Convert date-of-birth to age (Excel 2003)

Column U of my worksheet contains dates of birth
Cell T2 has a fixed date
I want to populate column T with age of each person, on the date in T2 - using the format yy:mm
For example if T2 is 26 Jul 2005 and cell U5 contains 30/10/1989 then I want T5 to be 15:10

I have come up with this very clumsy formula, that does the job, but I bet someone in this forum can show me how to do it elegantly.
<code>
=IF(U5>0,INT(((YEAR(T\$2)-YEAR(U5))*12+MONTH(T\$2)-MONTH(U5))/12) & ":" & MOD((YEAR(T\$2)-YEAR(U5))*12+MONTH(T\$2)-MONTH(U5), 12),"")
</code>

StuartR

2. ## Re: Convert date-of-birth to age (Excel 2003)

I'd say 15:8 instead of 15:10 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Here is a formula using the DATEDIF Function:

=IF(U5,DATEDIF(U5,\$T\$2,"y")&":"&DATEDIF(U5,\$T\$2,"y m"),"")

3. ## Re: Convert date-of-birth to age (Excel 2003)

Spot on, thanks.

The reason for the disparity of 2 months was because I used today's date in this example, but my worksheet used a date at the end of August, so I copied the wrong value!

StuartR

4. ## Re: Convert date-of-birth to age (Excel 2003)

FWIW, I prefer decimal years, set to be rounded to or displyed to whatever precision you prefer.
Something like
=(today()-dateofbirth)/365.25 seems to work
BUT nb oddities for really old folk, born before about 1920, who end up with negative ages.
At least, they do if you let excel import dates of birth from a list with 2-digit dates.
My workaround is to subtract 100 years from their quoted d.o.b then do the magic on that.
Happy days. I was still born in 20th century. Even Excel says so.

5. ## Re: Convert date-of-birth to age (Excel 2003)

In that case, you'd get more reliable results with something like:
=DATEDIF(D.O.B.,TODAY(),"y")+DATEDIF(D.O.B.,TODAY( ),"yd")/365
although for precision you should replace the '365' with a proper leap-year test

Cheers

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•