Convert a number in text to a valid date

[brianfosterblack]

Here is a sample of 2 identity numbers where the first 6 numbers are the date of birth YYmmDD,
The left 2 numbers are the year of birth, the next 2 are the month and the next 2 are the day.
Anyone where the left 2 numbers are between 00 and 20 were born from 2000 onwards
All others were born in the 1900s - So not one of the people in this list is older than 99
I need to have a formula to convert this to a valid date of birth DDmmYYYY

I have tried Date(YY,mm,DD) but that gives me all the birth dates in the 1900s.

Can anyone help.

4708040083 and 0306165018

 

[brianfosterblack]

Yeah, I was wondering who Dave was also. Anyway, for the formula I posted, this will assume numbers between 00 and the last two digits of the current year to be in the 2000s, all others will be assume to be in the 1900s...

=0+TEXT(19+(0+LEFT(A1,2)<0+TEXT(NOW(),"yy"))&LEFT(A1,6),"0000-00-00")

Sorry Rick, it was very late at night here in South Africa, so excuse the blonde moment. Thank you for the resolution to this problem, it works perfectly now. Thank you both for the help.