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]

are they all exactly the same format as text

 

[Rick Rothstein]

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

If your text data is always in the format you show, then you can use this formula to retrieve just the date (in serial form... use cell formatting to make it look like an actual date)...

=0+LEFT(A1,LEN(A1)-15)

 

[mole999]

two small observations

if i use the 0+ then I get #VALUE ! and when custom formatted to mmm dd yyyy the comma appears, none of which is relevant to me

 

[Rick Rothstein]

two small observations

if i use the 0+ then I get #VALUE ! and when custom formatted to mmm dd yyyy the comma appears, none of which is relevant to me

Are you referring to the formula I posted in Message #3 ? If so, what you say is happening to you does not happen for me... my formula works fine and the custom format you show does not display a comma. If it might matter, I am using the computer with US locale settings.

 

[tonywatsonhelp]

Hi Rick,

Thanks for your help but It doesn't seam to work? I just get a #value error? the text is exactly as shown?
I'm in the uk, if that effects anything ? using excel 2016 on a PC

 

[mole999]

UK settings here, I was expecting 0+ to work. (ISNUMBER) says the text is not a number

 

[offthelip]

this will do it, I put your date into L4

=DATE(MID(L4,9,4),MONTH(1&LEFT(L4,3)),MID(L4,5,2))

 

[Norie]

Rick

I also get #VALUE ! with your formula so it probably is a UK settings thing,

 

[JLGWhiz]

I am not sure you can use text as a basis for making another cell display custom format as a date data type. I believe the destination cell would have to have a date value entered and then be formatted to display as the custom format. I got the format to display easy enough, but the data type remained general.