Excel VBA или формула для преобразования текста в дату с типом времени

Question

У меня есть довольно много данных, где его дата / время были импортированы в Excel в виде текста. Excel на самом деле не распознает его как дату со временем. например, 25.08.2011 16:17:59 (мм.дд.гггг чч: мм: сс)

Теперь мне нужно преобразовать его в дату со временем, чтобы его можно было использовать для дальнейшего расчета с помощью Excel.

Как я могу это сделать? Текущий обходной путь состоит в том, чтобы "повторно войти" в материал. Означает, что если вы щелкнете по ячейке, чтобы активировать "правку", и нажмете "просто вернуться", то она автоматически преобразуется Excel в дату и время. Я могу автоматизировать этот процесс с помощью макроса VBA, приведенного ниже, но, хотя у меня довольно большой диапазон более 30 000, это занимает много времени, и вы не можете в это время изменять окно / или приложение, потому что оно работает через графический интерфейс.

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

Любые другие предложения, как автоматизировать этот материал намного быстрее? Я попробовал уже следующие другие возможности, но не сработало:

• изменить формат ячейки на дату с помощью пользовательского формата mm.dd.yyyy чч: мм: сс - значение ячейки не будет автоматически преобразовано в дату
• используя формулу DATEVALUE(String): значение конвертируется, но оно просто конвертирует дату и время теряется, например 25.08.2011 00:00:00 (мм.дд.гггг чч: мм: сс)
• используя формулу TIMEVALUE (String), значение конвертируется, но оно просто конвертирует время и дату теряется, например 00.00.1900 16:17:59 (мм.дд.гггг чч: мм: сс)

• используя функцию VBA « Format () », например, значение изменяется при изменении формата, но все равно впоследствии распознается как текст, например

  Dim myVar as String myVar = Format (cell.value, "дд / мм / гггг чч: нн: сс")

• используя макрос VBA с ячейкой.FormulaR1C1 = "25.08.2011 16:17:59" но это тоже не работает. значение сохранить распознанным как текст

Спасибо за вашу помощь

Answer 1

Выберите ячейки и запустите:

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

Взломать: скопировать весь столбец в буфер обмена, вставить в блокнот, скопировать все из блокнота, вставить обратно в Excel. Убедитесь, что формат даты / времени на вашем компьютере совпадает с используемым там, иначе Excel не будет его интерпретировать.

Answer 3

Это работало нормально для меня

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

Просто на британском ПК это никак не понравилось 25.08.2011 из-за периода (.). Итак, я конвертирую точку в прямой слэш.

После этого я мог использовать код =Today(A1) и он распознал дату. Может быть, вам нужно отформатировать ячейки до даты после макроса.

Answer 4

Все значения могут быть сброшены сразу:

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

или замените любой символ на себя, чтобы переоценить значения:

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

Answer 5

Для формулы листа:

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

а затем отформатируйте его как хотите.

Если вы хотите использовать макрос VBA, предполагается, что

• Ваши данные начинаются с A1 и находятся в столбце A
• Результаты будут представлены в столбце B, но вы можете легко изменить код для перезаписи, как только увидите, что он работает нормально.
• Формат похож на ваши примеры: дата и время разделены одним пробелом; Компоненты даты разделены <точка>; компоненты времени разделены <двоеточием>

---

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