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

 

[Rick Rothstein]

Rick

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

Hmm! A date like October 16, 2017 is not a normal way to write a full date in the UK? How would you write that as a full date then?

Instead of 0+ does using DATEVALUE work instead...

=DATEVALUE(LEFT(A1,LEN(A1)-15))

 

[mole999]

Hmm! A date like October 16, 2017 is not a normal way to write a full date in the UK? How would you write that as a full date then?

Instead of 0+ does using DATEVALUE work instead...

=DATEVALUE(LEFT(A1,LEN(A1)-15))

British dates commence with the day number and then the month. the US form is actually the more traditional Anglo-Saxon way, but the British adapted to using the European form in the early 20th Century.

 

[Norie]

Rick

We'd probably write it as 16th October 2017, with the 'th' ordinal being optional.

 

[JLGWhiz]

this produces the format and date data type.

Sub t()
Dim v As Variant
v = Range("A3").Value
Range("AA3") = CDate(Left(v, InStr(v, "2015") + 3))
Range("AA3") = Format(Range("C3").Value, "mmm dd yyyy")
End Sub

 

[tonywatsonhelp]

Thanks everyone,
I got there in the end, offthelip, your suggestion of "=DATE(MID(L4,9,4),MONTH(1&LEFT(L4,3)),MID(L4,5,2))" worked perfectly but when the date was only one number not two still got an error so simply added an if error and the formula using 1 instead of two and it works perfectly.

thanks for your help everyone.
formula I used:
"=IFERROR(DATE(MID(A52651,9,4),MONTH(1&LEFT(A52651,3)),MID(A52651,5,2)),DATE(MID(A52651,9,4),MONTH(1&LEFT(A52651,3)),MID(A52651,5,1)))"

 

[Rick Rothstein]

Rick

We'd probably write it as 16th October 2017, with the 'th' ordinal being optional.

British dates commence with the day number and then the month. the US form is actually the more traditional Anglo-Saxon way, but the British adapted to using the European form in the early 20th Century.

Sheesh, you guys write short dates incorrectly (day/month/year) and now you write long dates the wrong way too.

Only kidding... only kidding!

While this formula would probably work for UK settings...

=0+MID(A1,5,2)&" "&REPLACE(LEFT(A1,12),5,4,"")

it would not work form US settings. The best bet then would be to use the formula offthelip posted in Message #8 as that looks like it would work in both locales.

EDIT NOTE: See Messages #15 and #17.

 

[Rick Rothstein]

Thanks everyone,
I got there in the end, offthelip, your suggestion of "=DATE(MID(L4,9,4),MONTH(1&LEFT(L4,3)),MID(L4,5,2))" worked perfectly but when the date was only one number not two still got an error so simply added an if error and the formula using 1 instead of two and it works perfectly.

thanks for your help everyone.
formula I used:
"=IFERROR(DATE(MID(A52651,9,4),MONTH(1&LEFT(A52651,3)),MID(A52651,5,2)),DATE(MID(A52651,9,4),MONTH(1&LEFT(A52651,3)),MID(A52651,5,1)))"

This shorter for formula should also work...

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

 

[mole999]

NASA lost a $125 million Mars orbiter because a Lockheed Martin engineering team used English units of measurement while the agency's team used the more conventional metric system for a key spacecraft operation, according to a review finding released Thursday.

We all need to agree on the same standard for the area we live and work in

 

[Norie]

I would actually use 2017-10-16.

 

[Rick Rothstein]

I would actually use 2017-10-16.

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.