Converting Text To Date in VBA

[M7A0S8T8E5R]

Good Morning,

I am looking to convert 4 digit text numbers to a month/year format that can be filtered.

Currently the dates will show up as 1014 for October 2014. I can not format to date, and when I use text to columns it doesn't format the entire column, about 95% will come through. Below is the code for the text to columns.

Columns("E:E").TextToColumns Destination:=Range("E1"), DataType:=xlFixedWidth, FieldInfo:=Array(0, 5), TrailingMinusNumbers:=True

Looking for any way to convert 100% of the dates. The other thing about the above method of converting to dates it assumes all dates are 2016 and does not work.

Any help would b greatly appreciated.

Thank you.

[Pete_UK]

One way is to use a formula, like this:

=DATE(2000+RIGHT(E1,2),LEFT(E1,2),1)

which will put each date as the 1st of the month. Format in the style you prefer, then copy down.

Hope this helps.

Pete

[M7A0S8T8E5R]

Thanks Pete!

That worked perfectly!