VBA to loop different length text dates to convert to Dates

[earlyfreak]

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.

[shg]

Welcome to the board.

	A	B	C
1	6232016	23 Jun 2016	B1: =--TEXT(A1, LOOKUP(LEN(A1), {4,5,7}, {"0-0-00","0-00-00","0-00-0000"}))
2	7312016	31 Jul 2016
3	10252016	25 Oct 2016
4	50216	02 May 2016
5	102315	23 Oct 2015
6	5316	03 May 2016

[earlyfreak]

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]

Literal arrays, yes.

You're welcome.

[earlyfreak]

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

[shg]

Unless your workbooks already use VBA, it would be gratuitous to have to handle all the associated issues just to replace a simple formula with a user-defined function. Create a Word doc and use it as a formulary. Mine is 404 pages long as of today.

[earlyfreak]

Thanks that's a great idea

[shg]

You're welcome.

[earlyfreak]

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.

Please Login or Register  to view this content.

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

[shg]

Please Login or Register  to view this content.

[earlyfreak]

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

[shg]

Then your data is ambiguous, and you need to fix it at the source.

[earlyfreak]

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,

[shg]

You're very welcome.