How do I calculate the age of a person from their ID no.?

Discussion in 'Microsoft Excel Programming' started by Tracey, May 29, 2009.

  1. Tracey

    Tracey Guest

    I have a work sheet which provides the ID number of a person (the first 6
    digits are the birth date of the person YYMMDD). Another column requests
    age. How can I create a formula that will automatically calculate the age of
    the person as at the current date?
     
    Tracey, May 29, 2009
    #1
    1. Advertisements

  2. Tracey

    Jacob Skaria Guest

    You can use the a formula...If yy is less that current year century is
    considered as 20.If yy is greater than current year century is considered as
    19..

    With the ID in A1 try the below formula and feedback


    =DATEDIF(DATE(IF(LEFT(A1,2)>TEXT(TODAY(),"YY"),"19"&LEFT(A1,2),"20" &
    LEFT(A1,2)),MID(A1,3,2),MID(A1,5,2)),TODAY(),"y")

    If this post helps click Yes
    ---------------
    Jacob Skaria


    "Tracey" wrote:

    > I have a work sheet which provides the ID number of a person (the first 6
    > digits are the birth date of the person YYMMDD). Another column requests
    > age. How can I create a formula that will automatically calculate the age of
    > the person as at the current date?
     
    Jacob Skaria, May 29, 2009
    #2
    1. Advertisements

  3. On Fri, 29 May 2009 04:18:01 -0700, Tracey <>
    wrote:

    >I have a work sheet which provides the ID number of a person (the first 6
    >digits are the birth date of the person YYMMDD). Another column requests
    >age. How can I create a formula that will automatically calculate the age of
    >the person as at the current date?


    Ah, your question brings to mind the Year 2000 problem, which was due in part
    to the custom of using only two digits to specify a year.

    If no one was born in this century, then:

    =DATEDIF(DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2)),TODAY(),"y")

    But that may only be true for an unknown number of years, so you are going to
    have to supply the possible age range for your persons in order to devise a
    formula that will continue to work for the foreseeable future, or change your
    ID's to use four digit years.
    --ron
     
    Ron Rosenfeld, May 29, 2009
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Wooders

    Dim person, needs help

    Wooders, Aug 1, 2003, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    327
    Dave Woodley
    Aug 4, 2003
  2. Jonsson

    Macro that hide or unhide and not calculate or calculate

    Jonsson, Aug 19, 2003, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    427
    Tom Ogilvy
    Aug 19, 2003
  3. Guest
    Replies:
    2
    Views:
    325
    Dave Peterson
    Sep 21, 2004
  4. Guest

    Activesheet.Calculate failing to calculate

    Guest, Oct 10, 2006, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    789
    Guest
    Oct 11, 2006
  5. HH

    Calculate age in years and month or decimal

    HH, Jun 24, 2008, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    2,462
Loading...

Share This Page