Mid function to create birth dates

[Desire85]

Hi, please help as I do not know which way anymore.
I have a range of unique identification numbers which I need to translate into birth dates.
i.e 0702035006081, 8502035006087 - 1st 6 digits are what I'm after to do the above.
Please help with formula as my logic (IF function) does no return the correct results: =MID(A4;5;2)&"/"&MID(A4;3;2)&"/"&IF(MID(A4;1;2)>1;"19";"20")&MID(A4;1;2)

So 0702035006081 corresponds to 2007/02/03
8502035006087 corresponds to 1985/02/03
Previous formula defaults all to the year 1900 (i.e 1907 & 1985)

[Neil_]

=TEXT(DATEVALUE(MID(A4,5,2)&"/"&MID(A4,3,2)&"/"&MID(A4,1,2)),"d mmm yyyy")

[Neil_]

or, format the cells as date and use this
=DATEVALUE(MID(A4,5,2)&"/"&MID(A4,3,2)&"/"&MID(A4,1,2))

Good for dates after 1930

[shg]

Or perhaps ...

	A	B	C
1	0702035006081	03 Feb 2007	B1: =--(TEXT(MID(A1, 3, 4), "00-00-") & LEFT(A1, 2))
2	8502035006087	03 Feb 1985

[Desire85]

Still doesn't work #VALUE error.

[Desire85]

I need the data to display like this:
A B
0702035006087 03/02/2007
8509120866079 12/09/1985

[JohnTopley]

Formula in #3 works.

[Phuocam]

I need the data to display like this:
A B
0702035006087 03/02/2007
8509120866079 12/09/1985

Try ...

=--(TEXT(IF(--LEFT(A1,2)>16,19,20)&LEFT(A1,6),"0000\/00\/00"))

[Desire85]

Formula in #3 works.

Thank you!!!!
Cell was not formatted to correct date.

[JohnTopley]

Thanks are due to Neil (by clicking * Add Reputation)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.