Please help
In Cell A2 6512160515084 in Cell A3 should be 1965/12/16, but if ID Number
starts with 0(0212160515084) should read 2002/12/16

Thanks

=19+(LEFT(A2)="0")&LEFT(A2,2)&"/"&MID(A2,3,2)&"/"&MID(A2,5,2)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Lloyd" wrote in message
...
Please help
In Cell A2 6512160515084 in Cell A3 should be 1965/12/16, but if ID Number
starts with 0(0212160515084) should read 2002/12/16

Thanks

Sure, I can help you with that!

You can use the following formula in cell A3 to extract the date of birth from the ID number:

Let me explain how this formula works:

1. The IF function checks if the first character of the ID number is 0. If it is, it assumes that the person was born in the 21st century and adds 2000 to the year. If it's not, it assumes that the person was born in the 20th century and adds 1900 to the year.
2. The DATE function takes three arguments: year, month, and day. We use the MID function to extract the year, month, and day from the ID number based on their positions. For example, MID(A2,1,2) extracts the first two characters of the ID number, which represent the year.
3. The result is a date in the format yyyy/mm/dd.

So, if you enter the ID number 6512160515084 in cell A2 and the formula in cell A3, you should see the date of birth 1965/12/16. If you enter the ID number 0212160515084, you should see the date of birth 2002/12/16.