CODEWITHSUNDEEP
excelvba
user15005577
user15005577

Reputation:

My excel file is in Germany(German) format and would like to change to English(UK) format

I have tried multiple codes such as this

enter image description here

Sub DateFixer()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
        For Each r In Selection
            v = r.Text
            r.Clear
            r.NumberFormat = "dd/mm/yyyy hh:mm:ss"
            r.Value = DateSerial(Mid(v, 7, 4), Mid(v, 4, 2), Left(v, 2)) + TimeSerial(Mid(v, 12, 2), Mid(v, 15, 2), Right(v, 2))
        Next r
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

and this

Application.ActiveWorkbook.Worksheets("data").Range("A1:A3").NumberFormat = "dd/mm/yyyy hh:mm:ss am/pm
"

and this

Sub changeformat()

    Dim lastrow As Long
    lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    'MsgBox lastrow
    For i = 2 To lastrow
        Cells(i, 2).NumberFormat = ("dd/mm/yyyy hh:mm:ss am/pm")
    Next i

End Sub

But it still did not change my DateTime to 9/12/20 09:28:00 am. May i know what did i do wrongly?

enter image description here

Upvotes: 1

Views: 565

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

As shown by the fact that, when in General format, the cell is still displaying a date/time string, the date/time stamps are Text strings and not "real" Excel dates (which are stored as days and fractions of a day).

First we have to convert the string to a "real" date. Then one can use the .NumberFormat property to display it in a cell however we want.

Fortunately, the format seems fixed so we can split on the dot/space and colon to get the different date/time parts.

The code below assumes

  • all the dates are text strings
  • the number format codes for dates are mdy and for time hms

As written, the results will be written in the adjacent column. To change it in place, see the comments in the code.

Option Explicit
Sub dateFixer()
    Dim vSrc As Variant, vRes As Variant
    Dim rSrc As Range, rRes As Range
    Dim V, W, X, I As Long
    
With ActiveSheet
    Set rSrc = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
    
    'set rRes = rSrc to overwrite original
    Set rRes = rSrc.Offset(rowoffset:=0, columnoffset:=1)
End With

'read into vba array for speedy processing
vSrc = rSrc

'Dim output array
ReDim vRes(1 To UBound(vSrc, 1), 1 To UBound(vSrc, 2))

'process the string dates
For I = 1 To UBound(vSrc, 1)
    V = Split(vSrc(I, 1), ".") '0=day, 1 = month
    W = Split(V(2), " ")  ' 0=year
    X = Split(W(1), ":")   ' 0,1,2 = hr:min:sec
    
    vRes(I, 1) = DateSerial(W(0), V(1), V(0)) + TimeSerial(X(0), X(1), X(2))
Next I

'Write the results back to the worksheet
Application.ScreenUpdating = False
With rRes
    .EntireColumn.Clear
    .Value = vRes
    .NumberFormat = "dd/mm/yy hh:mm:ss"
End With

End Sub

enter image description here

Upvotes: 1

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11998

You can do it splitting each part and forcing them into dates. Then sum up and then change format:

enter image description here

Sub changeformat()

Dim lastrow As Long
Dim i As Long

lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow
    Cells(i, 1).Value = CDate(Replace(Split(Cells(i, 1), " ")(0), ".", "/")) + CDate(Split(Cells(i, 1), " ")(1))
    Cells(i, 1).NumberFormat = ("dd/mm/yyyy hh:mm:ss am/pm")
Next i

End Sub

After executing code I get:

enter image description here

Notice the value 9.12.20 09:27:60 can't be converted because 09:27:60 is not a valid time. Seconds always go from 00 to 59, not from 1 to 60. So actually 09:27:60 should be 09:28:00

I'm afraid you'll need to code an exception for this cases.

Upvotes: 1

Related Questions