Converting a string formatted date and time to readable values using vba

[MahTah]

Hello All,

I have a datasheet in form of Excel table with different columns of data where I want to convert the first column (Time) including date and time information in each cell from string format to readable date and time format using VBA.
Time
30.11.2016 14:20:44
30.11.2016 14:20:45
30.11.2016 14:20:46
30.11.2016 14:20:47
30.11.2016 14:20:48
30.11.2016 14:20:49

I can do this simply on Excel using "Text to Columns" option, and therefore I tried to record a macro out of this method which looks like this:

Sub Macro3()
    Range("Table2[Time]").Select
    Selection.TextToColumns Destination:=Range("A4"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub

However, when executed, this code doesn't do the job and the cells remain in string format.
I also don't want to do the task using iterative methods since for a big set of data it can be costly. I would appreciate if you have an idea how to accomplish the task.

Regards,

 

[sergioMabres]

Hi MahTah
Here is a way to do what you want, but remember the macro depends on the date and time format defined in your machine
so replacing dot with slashes that is needed in my PC may not be needed in yours

Sub str2datetime()
   Dim r, c As Range
   Dim f As String
   Dim s, l As Integer
   Set r = Selection
   For Each c In r
      l = Len(c.Text)
      s = InStr(c.Text, " ")
      f = "=datevalue(""" & Mid(Replace(c.Text, ".", "/"), 1, s - 1) & """)+timevalue(""" & Right(c.Text, l - s) & """)"
      c.Formula = f
      c.NumberFormat = "dd/mm/yyyy hh:mm:ss"
   Next c
End Sub

Select a range and call the macro

Cheers
Sergio

 

[Fennek]

Hi,

depending on country-settings, this could be a proper xl-date. Please try to apply the numberFormat (strg-1) to standard (maybe generl). If you see a number 40.000 something, then it is already a xl-date. If not, try just numberFormat "date", maybe it converts automatically.

regards

 

[Tetra201]

Try this (not tested):

Sub Convert()
    Range("Table2[Time]").TextToColumns Destination:=Range("A4"), _
    DataType:=xlDelimited, Space:=True, FieldInfo:=Array(1, 4)
    Range("Table2[Time]").AutoFit
End Sub