![Excel VBA 或公式将文本转换为带有时间类型的日期](https://linux22.com/image/1430480/Excel%20VBA%20%E6%88%96%E5%85%AC%E5%BC%8F%E5%B0%86%E6%96%87%E6%9C%AC%E8%BD%AC%E6%8D%A2%E4%B8%BA%E5%B8%A6%E6%9C%89%E6%97%B6%E9%97%B4%E7%B1%BB%E5%9E%8B%E7%9A%84%E6%97%A5%E6%9C%9F.png)
我有相当多的数据,其日期/时间戳已作为文本导入 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 将无法解释它。