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
digits.
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.

Answer: Converting Identification Number to Birthday
 

Here's the formatted text:

You can use the following formula in cell B1 to extract the day and month of the person's birthdate:

Let me break down the formula for you:

1. The IF function checks if the first character in cell A1 is "P". If it is, it returns an empty string (""), which means it will do nothing.
2. If the first character in cell A1 is not "P", it means that the data is in the format of the ID number. The DATE function is used to convert the first 6 digits of the ID number into a date value.
3. The TEXT function is used to format the date value into the desired format of "dd mmm". The "dd" represents the day of the month, and the "mmm" represents the abbreviated month name (e.g. Jan, Feb, Mar).

Once you enter this formula into cell B1, it will automatically extract the day and month of the person's birthdate if the data in cell A1 is in the format of the ID number. If the data in cell A1 is in the format of the Passport Number, it will return an empty string.

Converting Identification Number to Birthday
 

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

=IF(LEFT(A3,1)<"P",DATE(MID(A3,1,2),MID(A3,3,2),M ID(A3,5,2)),"")


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


"WildWill" wrote:

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
digits.
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.

Converting Identification Number to Birthday
 

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



WildWill wrote:

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
digits.
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.

--

Dave Peterson

Converting Identification Number to Birthday
 

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" wrote:

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



WildWill wrote:

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
digits.
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.

--

Dave Peterson

Converting Identification Number to Birthday
 

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

=--TEXT(IF((--LEFT(A1,2))<30,"20","19")&LEFT(A1,6),"0000\-00\-00")

with a custom number format of:
dd mmmm yyyy



WildWill wrote:

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" wrote:

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



WildWill wrote:

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
digits.
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.

--

Dave Peterson


--

Dave Peterson