☰

Excel VBA 或公式将文本转换为带有时间类型的日期

tag-icon • tag-icon microsoft-excel worksheet-function vba date-time
Excel VBA 或公式将文本转换为带有时间类型的日期

我有相当多的数据，其日期/时间戳已作为文本导入 Excel。Excel 无法真正将其识别为带有时间的日期。例如 25.08.2011 16:17:59 (mm.dd.yyyy hh:mm:ss)

现在我需要将其转换为带有时间的日期，以便可以使用 Excel 进行进一步计算。

我该怎么做？目前的解决方法是“重新输入”内容。这意味着如果您单击单元格以激活“编辑”并按下回车键，Excel 会自动将其转换为日期时间。我可以通过下面的 VBA 宏自动执行这些操作，但虽然我的范围非常大，超过 30'000，但这需要很长时间，而且您无法同时更改窗口/或应用程序，因为它是通过 GUI 工作的。

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

还有其他建议如何更快地实现自动化吗？我已经尝试了以下其他可能性，但没有奏效：

  • 使用自定义格式 mm.dd.yyyy hh:mm:ss 将单元格格式更改为日期 - 单元格值不会自动转换为日期
  • 使用公式 DATEVALUE（String）：值被转换但它只转换日期并且时间丢失例如 25.08.2011 00:00:00（mm.dd.yyyy hh:mm:ss）
  • 使用公式 TIMEVALUE（String）值被转换但它只转换时间并且日期丢失例如 00.00.1900 16:17:59（mm.dd.yyyy hh:mm:ss）

  • 使用 VBA 函数“格式（）“例如值改变格式但之后仍然被识别为文本例如

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

  • 使用带有 cell.FormulaR1C1 = “25.08.2011 16:17:59” 的 VBA 宏，但它也不起作用。值保持被识别为文本

感谢您的帮助

答案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

答案2

这对我来说很好

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

简单来说，在英国的 PC 上，它不喜欢 25.08.2011，因为有句号 ( .)。因此，我将点转换为正斜杠。

此后，我可以使用代码=Today(A1)，并且它识别了日期。也许您需要在宏之后将单元格格式化为日期。

答案3

对于工作表公式：

=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

答案4

技巧：将整个列复制到剪贴板，粘贴到记事本中，从记事本复制所有内容，然后粘贴回 Excel。确保您的计算机日期/时间格式与那里使用的格式匹配，否则 Excel 将无法解释它。

相关内容