Excel VBA执行PasteSpecial的Add运算转换文本日期效果不一致

问题出现的核心原因 #

• 系统区域日期格式不匹配：你导出的日期字符串格式为dd/mm/yy hh:mm:ss，如果当前Windows系统的短日期默认格式为mm/dd/yy，Excel自动转换时只会将「日的数值≤12」的字符串识别为合法日期，「日的数值>12」的字符串会被判定为无效日期、保持文本格式，12/31≈38%的转换率和你提到的35%基本吻合，是该问题的最典型诱因。
• 单元格格式残留：专有软件导出时可能给部分单元格设置了强制文本格式，就算执行加法运算也不会自动转换为日期格式。
• 隐藏异常字符：导出的字符串首尾可能带有不可见的空格、ASCII 160不间断空格等非打印字符，导致Excel无法识别为日期。

可行的替代解决方案 #

1. VBA强制格式解析方案（效率最高，18000行数据秒级完成） #

直接按dd/mm/yy hh:mm:ss的规则拆分字符串转换，不受系统区域格式影响：

Sub 批量转换导出日期()
    Dim arr As Variant, i As Long, j As Long
    Dim datePart As String, timePart As String
    Dim targetRng As Range
    
    ' 定义需要转换的目标区域
    Set targetRng = Range("A8", Range("A8").SpecialCells(xlLastCell))
    ' 读取数据到数组，比逐单元格操作效率高数十倍
    arr = targetRng.Value
    
    For i = 1 To UBound(arr, 1)
        For j = 1 To UBound(arr, 2)
            ' 清除隐藏异常字符
            arr(i, j) = Trim(Replace(arr(i, j), Chr(160), ""))
            If Len(arr(i, j)) > 10 Then ' 判断是否符合日期字符串长度
                datePart = Split(arr(i, j), " ")(0)
                timePart = Split(arr(i, j), " ")(1)
                ' 按dd/mm/yy规则强制拼接为日期，可根据年份范围调整加2000还是1900
                arr(i, j) = DateSerial(Right(datePart, 2) + 2000, Mid(datePart, 4, 2), Left(datePart, 2)) + TimeValue(timePart)
            End If
        Next j
    Next i
    
    ' 写回数据并统一设置日期格式
    targetRng.Value = arr
    targetRng.NumberFormat = "yyyy/mm/dd hh:mm:ss" ' 可自定义需要的显示格式
    Application.CutCopyMode = False
End Sub

2. 无代码Power Query方案 #

不需要编写VBA，稳定性更高：

• 选中数据区域，点击「数据」选项卡→「从表格/区域」导入Power Query编辑器
• 选中所有需要转换的日期列，右键→「更改类型」→「使用区域设置」
• 类型选择「日期/时间」，区域选择「英语（英国）」（该区域默认日期格式为dd/mm/yy），确认后导出回Excel即可。

内容的提问来源于stack exchange，提问作者D. R. M. Smith