# 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. ### TraceyGuest

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

2. ### Jacob SkariaGuest

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

3. ### Ron RosenfeldGuest

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