CharmaineSilver

5 years agoQrew Member

# Formula to convert part of number to date

Hi

In South Africa we have Identity numbers which are 13 digits, the first 6 digits are your date of birth eg ID number is 8404130028085, then date of birth is 13 April 1984.

What formula(s) can I use to extract the first 6 numbers - I tried - (Left, Field, 6), then add the century (ie 19, or 20 if the first 2 numbers are less than 20) and then convert it to a date. What type of field must the ID Number be? I tried number but then Left wouldn't work, changed to text but now just doesnt give an answer although it accepts the formula. Also wht type of field must this date of birth be- I have it as formula- date.

Any help most appreciated.

Thanks

------------------------------

Charmaine Silverman

------------------------------

In South Africa we have Identity numbers which are 13 digits, the first 6 digits are your date of birth eg ID number is 8404130028085, then date of birth is 13 April 1984.

What formula(s) can I use to extract the first 6 numbers - I tried - (Left, Field, 6), then add the century (ie 19, or 20 if the first 2 numbers are less than 20) and then convert it to a date. What type of field must the ID Number be? I tried number but then Left wouldn't work, changed to text but now just doesnt give an answer although it accepts the formula. Also wht type of field must this date of birth be- I have it as formula- date.

Any help most appreciated.

Thanks

------------------------------

Charmaine Silverman

------------------------------