Change format of exported data

[dabon]

Hi,
the exported data is dated in this format 2015-04-22T12:07:13.059Z and I want to convert it in gg/mm/aaaa. It does not work from Format Cells menu, maybe because the starting data seems a string.
Can you please suggest me a solution?

Thanks

 

[Cubist]

Try:

=--(MID(A1,9,2)&"/"&MID(A1,6,2)&"/"&LEFT(A1,4))

 

[DanteAmor]

Maybe:

varios 10nov2024.xlsm
	A	B	C	D
1
2		2015-04-22T12:07:13.059Z	22/04/2015
3
Hoja4

Cell Formulas
Range	Formula
C2	C2	=DATE(LEFT(B2,4),MID(B2,6,2),MID(B2,9,2))

 

[Alex Blakenburg]

That is an ISO standard date time format so you have lots of options in addition to the above.
• Find T* and Replace with nothing
• Text to Columns
- Either fixed width up to the T
- Or delimited using the T
In both cases tell it to skip the 2nd column.
• Another formula

=--LEFT(A1,10)

• VBA

Sub ConvertISODate()
    ActiveSheet.UsedRange.Columns("A").Replace what:="T*", replacement:=""
End Sub