change date "m/d/yy h:mm" format to "dd/mm/yyyy hh:mm" using VBA

[Mike Zagle]

Hi All, I have been trying to change the format of the date with a vba code since " format cells" is not working for the current purpose. , I have tried to select and replace but it is time consuming with more than 8000 observations with different dates.
```

Sub changes()
   Dim trial As Variant
   Dim base() As String
   Dim i As Integer
  
      trial = ActiveSheet.Range("A3").Select
       i = ActiveSheet.Range("B3").Select
  
       base = Split(trial, "/")
    
      If Len(base(2)) = 2 Then
     i = base(1) & "/" & base(0) & "/" & base(2)
     End If
  
End Sub

``` I have tried this but it is not working... I am new to VBA and would like to have a code to convert my date. Thanks

 

[Luke M]

Hi Mike, and welcome to the forum.

Before trying to use VB to split the data and put it back together, I would suggest using the native XL feature. It's somewhat hidden, but on the 3rd screen of the Text-to-columns tool (pick delimited on first, skip 2nd screen), is the option to re-interpret dates.



I'd suggest giving that a try first. If you need this as part of a larger macro, you could always record this action.

 

[Mike Zagle]

Thanks Luke it does work

 

[Deepak]

@Mike Zagle

Pls also use code tags while posting codes on the post, i have did it for now...