CODEWITHSUNDEEP
excelvba
Zerrets
Zerrets

Reputation: 53

Format Date VBA

i want to implement this recorded macro into a macro for my code, i succesfully transformed "E" row into general, and i want to change that date into Short Date format DD/MM/YYYY the macro i recorded is this one below:

Sub Macro2()
'
' Macro2 Macro

    Range("L4").Select
    ActiveCell.FormulaR1C1 = "=DATEVALUE(MID(RC[-7],1,10))"
    Range("L4").Select
    Selection.AutoFill Destination:=Range("L4:L4500"), Type:=xlFillDefault
    Range("L4:L4500").Select
    Selection.NumberFormat = "m/d/yyyy"
End Sub

I tried it by making the function into the L Column, if it is possible i would like to implement it in one column so all values change and then paste them into the E column

The whole E column is like this:

And i want them to change into

This the code i used to transform the whole E column data to the format of dd-mm-yyyy hh:mm:ss to correct the error of some data not changing into the correct format

With ActiveSheet.UsedRange.Columns("E").Cells
    Columns("E").NumberFormat = "0"
    Columns("E").NumberFormat = "General"
End With

Upvotes: 0

Views: 356

Answers (1)

FaneDuru
FaneDuru

Reputation: 42246

If in '01-10-2019 52:59:76' first two digits means day, try please the next code:

Sub testDateFormat()
  Dim lastRow As Long, sh As Worksheet, x As String, i As Long
    Set sh = ActiveSheet 'use here your sheet, if not active one
     lastRow = sh.Range("E" & sh.Rows.count).End(xlUp).Row
     sh.Range("E1:E" & lastRow).NumberFormat = "dd/mm/yyyy"

    For i = 2 To lastRow
        If sh.Range("E" & i).Value <> Empty Then
            If chkFind(CStr(sh.Range("E" & i).Value)) = True Then
               x = CStr(sh.Range("E" & i).Value)
               sh.Range("E" & i).Value = Format(DateSerial(Split(left(x, WorksheetFunction.Find(" ", x) - 1), "-")(2), _
                           Split(left(x, WorksheetFunction.Find(" ", x) - 1), "-")(1), _
                           Split(left(x, WorksheetFunction.Find(" ", x) - 1), "-")(0)), "dd/mm/yyyy")
            Else
                Debug.Print "Unusual string on the row " & i
            End If
        End If
    Next i
End Sub
Private Function chkFind(strVal As String) As Boolean
    On Error Resume Next
     If WorksheetFunction.Find(" ", strVal) = 11 Then
        chkFind = True
        If Err.Number <> 0 Then
           Err.Clear
           On Error GoTo 0
           chkFind = False
        End If
     Else
        chkFind = False
     End If
     On Error GoTo 0
End Function

If first digits represents month, then the last two array (split) elements must be vice versa:

Split(left(x, WorksheetFunction.Find(" ", x) - 1), "-")(0), _ Split(left(x, WorksheetFunction.Find(" ", x) - 1), "-")(0))

instead of

Split(left(x, WorksheetFunction.Find(" ", x) - 1), "-")(1), _ Split(left(x, WorksheetFunction.Find(" ", x) - 1), "-")(1))

Upvotes: -1

Related Questions