Results 1 to 5 of 5
  1. #1
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    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. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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