Change whole column data format from yyyy-mm-dd hh:mm to dd/mm/yyyy VBA

Question

I am trying to convert the data format from yyyy-mm-dd hh:mm to dd/mm/mmmm I've been trying different solutions but none of them works, the closest one was this:

Columns("I:I").Replace What:=" *", Replacement:="", LookAt:=xlPart
Columns("I:I").TextToColumns Destination:=Range("11"), _
DataType:=xlDelimited, FieldInfo:=Array(1, xlDMYFormat)
Columns("I:I").NumberFormat = "dd/mm/yyyy"

But it throws me an error in this two lines:

   Columns("I:I").Replace What:=" *", Replacement:="", LookAt:=xlPart
  Columns("I:I").TextToColumns Destination:=Range("11"), _

"...it throws me an error..." Why not provide the message from that error? — Marc, Jan 20 '20 at 17:37
Why are you using VBA for this when you can use a custom number format in Excel? — jclasley, Jan 20 '20 at 17:43
I would suggest that you start a macro recording, then change the format as per @jclasley suggestion, then when you stop recording you can inspect the code in VBA developer tab — Plato77, Jan 20 '20 at 17:50
Does Excel understand the dates as such, or it thinks it's text? If it understands the dates as `Date` values, all you need is a `NumberFormat` - avoid treating dates as strings, your life will be much simpler. — Mathieu Guindon, Jan 20 '20 at 18:14
Columns("A:A").Select Selection.NumberFormat = "dd\/mm\/yyyy" — Paweł Piwowar, Jan 20 '20 at 18:21

Paweł Piwowar · Answer 1 · 2020-01-20T18:28:23.503

0

Columns("A:A").Select

Selection.NumberFormat = "dd\/mm\/yyyy"

and back

Selection.NumberFormat = "yyyy-mm-dd"

edited Jan 20 '20 at 18:28

answered Jan 20 '20 at 18:23

Paweł Piwowar

174
2
8

Although your suggestions will work, there is no good reason to use [`Select` or `Selection`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), and there is no good reason to [escape the forward slashes](https://support.office.com/en-us/article/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68?ui=en-US&rs=en-US&ad=US). – Ron Rosenfeld Jan 20 '20 at 18:56
Dear Ron, this example comes from the macro recorder. No letter is my authorship :-) – Paweł Piwowar Jan 22 '20 at 12:01
Most of the time, you need to edit the output from the macro recorder in order to turn it into good code. – Ron Rosenfeld Jan 22 '20 at 12:37
You might also want to read this article about [The Macro Recorder Curse](https://rubberduckvba.wordpress.com/2019/06/30/the-macro-recorder-curse/) – Ron Rosenfeld Jan 22 '20 at 12:45

score 0 · Accepted Answer · answered Jan 20 '20 at 19:20

This might help.

Sub test()
On Error Resume Next
Columns("I:I").Replace What:=" *", Replacement:="", LookAt:=xlPart
Columns("I:I").TextToColumns Destination:=Range("11"), _
DataType:=xlDelimited, FieldInfo:=Array(1, xlDMYFormat)
Columns("I:I").NumberFormat = "dd/mm/yyyy"
End Sub

Change whole column data format from yyyy-mm-dd hh:mm to dd/mm/yyyy VBA

2 Answers2