Convert 15 Oct 2019 16:23:28 GMT+00:00 to 15/10/2019

[kachaloo]

Hello
I have downloaded a report and the date in the above format stored as text.
I would like to change it 15/10/2019 to create a Pivot table.
I have tried datevalve, format the cell to date but no joy.
Any pointer please.
thank you.

 

[DanteAmor]

How about

varios 12abr2020.xlsm
	A	B
1
2	15 Oct 2019 16:23:28 GMT+00:00	15/10/2019
Hoja4

Cell Formulas
Range	Formula
B2	B2	=LEFT(A2,11)+0

 

[kachaloo]

Wow @DanteAmor . It was that simple.
I really need to locked down myself now.
Thanks

 

[Peter_SSs]

If you wanted to do it "in place" (ie in the column the text data already is) you could run this one-line macro or manually do a Text to Columns (can provide further instructions for the manual process if required). Just need to ensure this macro is pointed at the correct column. I have assumed column A.

Sub ConvertToDate()
    Columns("A").TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 4), Array(11, 9))
End Sub