Converting Identification Number to Birthday



I have a cell (A1) that could contain one of two types of data:

1) Passport Number which is in the format of "PXXXXX" where X = numeric
2) a standard ID number which has the format "YYMMDD XXXX XX X" where the
first 6 digits represent the persons birth date. I need a formula that will
look into the data entered into A1, and:

If the entered data is in the format of the Passport Number, do nothing.
If the entered data is in the format of the id number, extract the day and
month of the persons birthdate and place that data in B1, with the format '15
Feb" - not showing the year value.

Jacob Skaria

Apply this formula in B1 and drag to other rows. Format Column B Cells to
Date dd-mmm format


If this post helps click Yes

Dave Peterson

IF(LEFT(A1,1)="P","",TEXT(DATE(YEAR(2000),MID(A1,3,2),MID(A1,5,2)),"dd mmm")))


Thanks Guys, this worked 100%!

Much on the same trend, consider the same format of ID number as provided
for the above query: 6701115110089, where the first 6 digits are still
YYMMDD. I now need to also derive the actual full birth date from this id
number, to arrive at the format (for this eaxmple) of "11 January 1967".
Please help with this one.

Dave Peterson

I would return a real date and then use a numberformat to show the date nicely:


with a custom number format of:
dd mmmm yyyy

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question