Excel VBA or Formula to convert text to Date with Time type

Question

I have a quite a number of data where its date/timestamp has been imported into excel as text. Excel does not really recognize it as date with time. e.g. 25.08.2011 16:17:59 (mm.dd.yyyy hh:mm:ss)

Now I need to convert it to a date with time so it can be used for further calculation with excel.

How can I do that? The current workaround is to "re-enter" the stuff. Means if you click into the cell to activate the "edit" and press just return then it gets automatically converted by Excel to a date time. I can automate the stuff by the VBA macro below, but while I have a quite a huge range over 30'000 it takes long time and you can not change the window / or app in the mean time because it works through the gui.

 Set c = Range("A1").Cells
 c.Select
 For row = c.row To 30000
     SendKeys "{F2}", True
     SendKeys "{ENTER}", True
 Next row

Any other suggestions how to automate this stuff much quicker? I tried already the following other possiblities but did not work:

• change ther format of the cell to date with custom format mm.dd.yyyy hh:mm:ss - cell value get not automatically converted to date
• using formula DATEVALUE(String) : the value get converted but it just converts the date and the time gets lost e.g. 25.08.2011 00:00:00 (mm.dd.yyyy hh:mm:ss)
• using formula TIMEVALUE(String) the value get converted but it just converts the time and the date gets lost e.g. 00.00.1900 16:17:59 (mm.dd.yyyy hh:mm:ss)

• using VBA function "Format()" e.g. value get changing the format but still afterwards recognized as text e.g.

  Dim myVar as String myVar = Format(cell.value, "dd/mm/yyyy hh:nn:ss")

• using VBA macro with cell.FormulaR1C1 = "25.08.2011 16:17:59" but it does not work either. value keep recognized as text

Thanks for your help

Answer 1

Select the cells and run:

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

Answer 2

This worked fine for me

Sub dothis()

Dim row As Integer
row = 0

Dim maxRows As Integer
maxRows = 3

For row = 1 To maxRows
    Range("A" & row).Value = Replace(Range("A" & row).Value, ".", "/")
Next row

End Sub

Simply, on a UK PC any way, it didn't like 25.08.2011 because of the period (.). So, I convert the dot to a forwardslash.

After this, I could use the code =Today(A1) and it recognized the date. May be you need to format the cells to Date after the macro.

Answer 3

For a worksheet formula:

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+TIME(MID(A1,12,2),MID(A1,15,2),RIGHT(A1,2))

and then format it as you like.

If you want to use a VBA macro, the following assumes

• Your data starts in A1 and is in column A
• The results will go in Column B -- but you can easily change the code to overwrite, once you see it is working ok
• The format is similar to your examples: Date and Time are separated by a single space; Date components are separated by < dot >; time components are separated by < colon >

---

Option Explicit
Sub ConvertDateTimeString()
    Dim vraw, vRes()
    Dim vDT, vTime, V
    Dim DT As Date, TM As Date
    Dim I As Long, S As String

vraw = Range("a1", Cells(Rows.Count, "A").End(xlUp))
    If VarType(vraw) < vbArray Then 'check for only one entry
        ReDim vraw(1 To 1, 1 To 1)
        vraw(1, 1) = [a1]
    End If
ReDim vRes(1 To UBound(vraw), 1 To 1)

For I = 1 To UBound(vraw)
    S = vraw(I, 1)
    vDT = Split(Split(S)(0), ".")
    vTime = Split(Split(S)(1), ":")

    DT = DateSerial(vDT(2), vDT(1), vDT(0))
    vTime = TimeSerial(vTime(0), vTime(1), vTime(2))
    vRes(I, 1) = DT + vTime
Next I

With Range("B1").Resize(UBound(vRes), 1)
    .EntireColumn.Clear
    .Value = vRes
    .NumberFormat = "dd.mm.yyyy hh:mm:ss"
    .EntireColumn.AutoFit
End With
End Sub

Answer 4

All values can be re-set at once:

With Range("A1:A30000")
    .NumberFormat = "dd.mm.yyyy hh:mm:ss"     ' optional to set the display format
    .Value = .Value
End With

or replace any character with itself to re-evaluate the values:

With Range("A1:A30000")
    .NumberFormat = "dd.mm.yyyy hh:mm:ss"     ' optional to set the display format
    .Replace " ", " "
End With

Answer 5

Hack: copy entire column to clipboard, paste into notepad, copy all from notepad, paste back into excel. Make sure your computer date/time format is matching the one used there, otherwise excel won't interpret it.