# Person ID convert to age

#### chrs32

##### New Member
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.
Code:
``=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..

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### CyrusTheVirus

##### Well-known Member
Welcome to the forums. Maybe try something like the below setup.

A2:
Code:
``=TODAY()``

B7:
Code:
``=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>

Last edited:

#### Rick Rothstein

##### MrExcel MVP
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

##### Well-known Member
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

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

#### Rick Rothstein

##### MrExcel MVP
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

##### Active Member
Hi,

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

Function BirthDay(Tx)
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

##### MrExcel MVP
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"

Last edited:

#### chrs32

##### New Member
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!!

Replies
3
Views
254
Replies
2
Views
2K
Replies
10
Views
652
Replies
9
Views
3K
Replies
8
Views
959

1,191,002
Messages
5,984,100
Members
439,872
Latest member
noaman79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back