Converting dates into formatted dates

Hello, I am working with a dataset that has non delimited dates that I want to convert into real dates.

I originally attempted to use =DATE(RIGHT(A1,4), MID(A1,3,2), LEFT(A1,2))

This worked for many of them. The issues is that they are not all stored as 8 digits.

I also tried the text to columns with fixed width and also did not work.

Some are 6 7 or 8.

mdyyyy

mddyyyy

mmdyyyy

mmddyyyy

I would like to convert these into mm/dd/yyyy if possible.

Any help would be great. Thank you so much!!

Edit: Thank you everyone for your help looking into this. The 7s seem to be the great issue. But I am going to try a combo of u/mrslobster and u/hg00000.

We are also going to contact the surveillance site about the issues without leading 0s on the dates. Thank you.