Turning text into dates with vba (or formula)

[tonywatsonhelp]

Hi Everyone,

So in column A I have text recorded like this "Oct 16, 2015 6:29:29 PM PDT"

in column AA I want it to show just as the date "Oct 16 2015" not text but date

Please help if you can

Thanks

Tony

 

[mole999]

ISO8001 is far better for sorting in directories and documents

 

[Norie]

Maybe I am missing the point, but the OP said his "dates" were text in the form of Oct 16, 2015 6:29:29 PM PDT, so that is what has to be modified in someway.

Rick

Sorry for going off topic, I was only commenting the date format I personally use.

 

[Rick Rothstein]

Rick

Sorry for going off topic, I was only commenting the date format I personally use.

Ah, okay, I see now. I thought you were making a comment about how the OP should have has data in the cells. Of course, if he had a choice, yes, 2017-10-16 format would be best as it is a universally recognized date format which removes any ambiguities.

 

[tonywatsonhelp]

This shorter for formula should also work...

=DATE(MID(L4,8,4),MONTH(1&LEFT(L4,3)),SUBSTITUTE(MID(L4,5,2),",",""))

Thanks Rick, this works great
Tony