Person ID convert to age

[chrs32]

Hello there,
I have a list of person ID which looks like - 05058010843
First 6 numbers being date of birth.
Everything works well for people born before 2000.

=INT(YEARFRAC(DATE(MID(B3,5,2),MID(B3,3,2),MID(B3,1,2)),DATE(2016,11,5))) & " years old"

If I try to apply same formula for ID: 20080426575
I get 112 years old..

 

[CyrusTheVirus]

Welcome to the forums. Maybe try something like the below setup.

A2:

=TODAY()

B7:

=IF(AND(MID(A7,5,2)>=0,MID(A7,5,2)<=RIGHT(YEAR($A$2),2)),INT(YEARFRAC(DATE(20&MID(A7,5,2),MID(A7,3,2),MID(A7,1,2)),$A$2)) & " years old",INT(YEARFRAC(DATE(MID(A7,5,2),MID(A7,3,2),MID(A7,1,2)),$A$2)) & " years old")

Today's Date
11/23/2016
ID's	Age
05058010843	36 years old
20080426575	12 years old

<tbody>
</tbody>

 

[Rick Rothstein]

Hello there,
I have a list of person ID which looks like - 05058010843

Terrible example... you have the same number for the month and day so we cannot tell if your dates are mmddyy or ddmmyy. When showing a single date, you should always use a day value greater than 12 so that we can easily tell the order of the month/day on your computer. Now, with that said, what is the earlier year value (19##) that you will ever need to process (we need to know this in order to properly handle the century switch point for a 2-digit year value)?

 

[CyrusTheVirus]

If I try to apply same formula for ID: 20080426575
I get 112 years old..

Hi Rick,

From what I gathered I think it's supposed to be day/month/year... because there is no month that is 20.

 

[chrs32]

Yes, sorry! It is day/month/year.
Earlier year is 1946 and 2015 for this century.

 

[Rick Rothstein]

Hi Rick,

From what I gathered I think it's supposed to be day/month/year... because there is no month that is 20.

Well "Duh!!"... I looked at the first date and never examined the second one.

 

[Fennek]

Hi,

try this function (UDF):
If ID in A1 then =Birthday(A1)

Function BirthDay(Tx)
'Link to RegEx 5.5
Dim Regex
Dim Tag As Date
Set Regex = CreateObject("vbscript.regexp")
Regex.Pattern = "(\d\d)(\d\d)(\d\d)\d+"

For Each R In Regex.Execute(Tx)
For Each SM In R.SubMatches
datum = datum & "," & Val(SM)
Next SM
Next R
BirthDay = Format(DateValue(Right(datum, Len(datum) - 1)), "YYYY/MM/DD")
End Function


regards
(sorry, I don't know about code-tags)

 

[Rick Rothstein]

Yes, sorry! It is day/month/year.
Earlier year is 1946 and 2015 for this century.

Assuming the values in Column B are text (so that the leading zeroes, when present, are really in the cell), give this formula a try...

=DATEDIF(DATE(MID(B2,5,2),MID(B2,3,2),LEFT(B2,2)),A$2,"y")-IF(0+MID(B2,5,2)<40,100,0)&" years old"

 

[chrs32]

Assuming the values in Column B are text (so that the leading zeroes, when present, are really in the cell), give this formula a try...

=DATEDIF(DATE(MID(B2,5,2),MID(B2,3,2),LEFT(B2,2)),A$2,"y")-IF(0+MID(B2,5,2)<40,100,0)&" years old"

Thank you so so much!
Works like a charm!!