Excel宏实现文本时间戳转指定格式报错（运行时错误13）解决

你的需求是把M/DD/YYYY hh:mm:ss.ms格式的文本时间戳（例如4/21/2023 16:03:41.791）转换为Excel可识别的YYYY-MM-DD h:mm:ss AM/PM格式（例如2023-04-21 4:03:41 PM），原代码触发运行时错误13（类型不匹配），原因是CDate无法直接解析带毫秒的时间字符串，Excel默认日期转换函数不支持这种带毫秒后缀的格式。

修改后的单单元格处理代码 #

Sub ConvertTimestamp()
    Dim originalTimestamp As String
    Dim timeWithoutMs As String
    Dim convertedTimestamp As Date
    
    ' 获取目标单元格的原始时间戳文本
    originalTimestamp = Range("O2").Value
    
    ' 剥离毫秒部分：找到最后一个小数点的位置，截取前面的日期时间内容
    timeWithoutMs = Left(originalTimestamp, InStrRev(originalTimestamp, ".") - 1)
    
    ' 转换为Excel日期格式，再设置目标显示格式
    convertedTimestamp = CDate(timeWithoutMs)
    Range("O2").Value = convertedTimestamp
    Range("O2").NumberFormat = "yyyy-mm-dd h:mm:ss AM/PM"
End Sub

批量处理整列（以O列为例） #

如果需要处理O列所有有数据的单元格，可以用这个版本：

Sub ConvertTimestampBatch()
    Dim cell As Range
    Dim originalTimestamp As String
    Dim timeWithoutMs As String
    
    ' 遍历O列从第2行开始的非空单元格
    For Each cell In Range("O2:O" & Cells(Rows.Count, "O").End(xlUp).Row)
        If cell.Value <> "" Then
            originalTimestamp = cell.Value
            ' 剥离毫秒部分
            timeWithoutMs = Left(originalTimestamp, InStrRev(originalTimestamp, ".") - 1)
            ' 转换并设置格式
            cell.Value = CDate(timeWithoutMs)
            cell.NumberFormat = "yyyy-mm-dd h:mm:ss AM/PM"
        End If
    Next cell
End Sub

关键说明 #

• 核心操作是先剥离毫秒部分：用InStrRev找到时间字符串最后一个.的位置，通过Left截取前面的日期和时分秒内容，这样CDate就能正常解析了。
• 最后设置单元格的NumberFormat，而不是直接转成文本，这样转换后的内容依然是Excel可识别的日期时间格式，方便后续计算或排序。

内容的提问来源于stack exchange，提问作者Matcha22