Extract date frm first 6 characters from right to Left

I am have tried setting up a formula to extract the date of birth from the first 6 characters starting from right to left



Eg 840723008147 after extraction should be 23/07/1984
200120305195 after extraction should be 20/01/2020


I need to se to the year as four characters, but the ID for the yea is only two characters

It would be appreciated if someone could assist me

If you wanted proper dates, then you could use this in B1:

=DATE(LEFT(A1,2)+IF(LEFT(A1,2)+0<30,2000,1900),MID(A1,3,2),MID(A1,5,2))

Note that this would return years in the range 1930 to 2029. but if these are dates of birth then presumably you would want to change the <30 to <21, to give a range from 1921 - 2020.

Hope this helps.

Pete

Or try:

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

Hi Glen

It goes back as far as 1930


Regards


Howard

Hi Guys

Many thanks for the help