0

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

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
0

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.

0

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
0

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.