VBA to loop different length text dates to convert to Dates

I get a CSV with multiply date columns like this
6232016
7312016
10252016
50216
102315
5316
my current approach is to us a if len is and separate into three columns and then join then convert to date then repeat for all other columns of the different text lengths. I have been searching the internet with no luck so I figure I would ask in this forum.
Looking for a better way to save me hours of pain

any direction would be greatly appreciated.

wow, that is amazing, are those } arrays within the formula?
I am forever grateful, you saved me hours from
=IF(LEN(A1)=7,LEFT(A1,1),LEFT(A1,2))
=IF(LEN(A1)=7,MID(A1,2,2),MID(A1,3,2))
=RIGHT(A1,4)
these formula
This is genius SHG B1: =--TEXT(A1, LOOKUP(LEN(A1), {4,5,7}, {"0-0-00","0-00-00","0-00-0000"}))

outstanding Thank you, Thank you

shg

Follow up Question if I may, how would I make that into a function and put it in my personal macro sheet so it is available all the time since I use it so much. Is that possible with an formula like this. Thanks again fantastic

Thanks that's a great idea

Since I fix dates daily, I was tired of copy and pasting formula so I created a loop. (I did start my word formula book)

Here it is, I prompt for column of dates and where to put fixed dates or overwrite them.

Sub FixDatesForReports()
Dim DateNumcol As Integer
Dim FixDateCol As Integer
Dim isnumber As Long
Dim MyCol As Long
Dim FinalRow As Long
Dim i As Long

Application.ScreenUpdating = False
On Error Resume Next

MyCol = ActiveCell.Column

DateNumcol = Application.InputBox(Prompt:="Enter Column Number where the Dates need to be fixed", Default:=MyCol, Type:=1)

FixDateCol = Application.InputBox(Prompt:="Enter Column Number where you wantfixed dates to be entered in", Default:=MyCol, Type:=1)


FinalRow = Cells(Rows.Count, DateNumcol).End(xlUp).Row

On Error Resume Next

FinalRow = Cells(Rows.Count, DateNumcol).End(xlUp).Row

For i = 2 To FinalRow

Cells(i, FixDateCol).Value = "=--TEXT(R[0]C[-1],LOOKUP(LEN(R[0]C[-1]),{4,5,7},{""0-0-00"",""0-00-00"",""0-00-0000""}))"

Next i


Application.ScreenUpdating = True
End Sub

Two things I want to work on.
1. Copying result in cell not the formula (so I do not have to copy and paste as values
2.They threw in another Date Day,Month 52016 with no day, just month and Year.

any additional input would be greatly appreciated to work with this great formula from Shg

Thanks shg

That works great for everything but the Date Day,Month 52016 with no day, just month and Year.
when I run the code it fixes all but makes 52016 into 5/20/2016 which is wrong. This is a weird format that come over in a csv

everything else is perfect. Don't know how to even attach this one because that could be a 5/20/16 date but comes across as just a month year

Thanks again for your help, direction and education

For that your correct, everything else works great, just tested again. The month year I will deal with as they come, easy enough

Thanks again for great solutions time savers, and fantastic formula and code

Thanks shg,