Похожие вопросы

Работа с часами и минутами в Excel 2007
Условное форматирование Excel для выделения всей строки, если одна дата не равна другой дате в этой строке
Операции с миллисекундами времени Excel 2007
Автоматическая синхронизация времени Windows XP при загрузке
Как преобразовать столбец integer в столбец time?
Windows 7 Время продолжает меняться само по себе
Среднее время в мм: СС.Формат 0
Excel: третья среда месяца
Подсчитать количество воскресений в месяце
Формула для обновления ячейки на основе изменения пользователем содержимого другой ячейки
Как создать пользовательский формат даты в нижнем колонтитуле, который автоматически обновляется в Powerpoint 2007?
Как добавить дату в часы панели задач без изменения ее размера?
Как установить синхронизацию даты и времени на fedora lxde?
Как отключить синхронизацию времени в Windows 7 в качестве хост-ОС в VirtualBox?
Автоматическая синхронизация времени Windows чаще, чем по умолчанию

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

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

теперь мне нужно преобразовать его в дату со временем, чтобы его можно было использовать для дальнейшего расчета с 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

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

    изменить там ячейки с пользовательский формат мм. ДД.гггг чч: мм: ss-значение ячейки не преобразуется автоматически в дату
  • использование формулы DATEVALUE (String): значение преобразуется, но оно просто преобразует дату и время теряется, например, 25.08.2011 00:00: 00 (mm.dd.гггг чч:мм:СС)
  • использование формулы TIMEVALUE (String) значение преобразуется, но он просто преобразует время и дата теряется, например, 00.00.1900 16:17:59 (mm.dd.гггг чч:мм:СС)
  • использование функции VBA "Format ()" например, значение получить изменение формата, но до сих пор впоследствии распознается как текст, например,

    Dim myVar как строка
    myVar = формат (ячейка.значение, "ДД/ММ/гггг чч:НН:СС")

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

Спасибо за помощь

5
задан megloff
источник

5 ответов

выделите ячейки и выполните команду:

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
1
отвечен Gary's Student 2014-11-13 14:05:26
источник

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

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 из-за периода (.). Итак, я преобразовать точку на forwardslash.

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

0
отвечен Dave 2014-11-13 13:45:55
источник

для Формулы:

=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
0
отвечен Ron Rosenfeld 2014-11-16 19:36:04
источник

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

0
отвечен JollyMort 2016-06-05 18:10:14
источник

все значения можно установить сразу:

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
0
отвечен Slai 2017-10-10 16:23:18
источник

Другие вопросы date-time microsoft-excel vba worksheet-function