Excel通过VBA将文本格式日期转为日期时间格式的问题

我懂你这种闹心的感觉——手动用分列把B列转成功了，结果用VBA自动化处理A列时，试了好几种方法都没效果，属实让人头疼。咱们先拆解下之前方法失效的原因，再给你两个靠谱的解决方案：

为什么之前的方法没用？ #

• Method1：Calculate只对公式单元格生效，你的A列是纯文本，所以完全没作用。
• Method2：你调用了分列，但没指定列的数据格式，Excel默认按常规格式处理，文本还是文本。
• Method3：用Format函数会把日期值转回字符串，表面上格式对了，但单元格本质还是文本，不是可计算的日期值。
• Method4：录制的宏里FieldInfo:=Array(1, 1)参数错了——1代表常规格式，得改成对应日期格式的枚举值，Excel才会识别文本里的日期信息。

方案1：模拟手动分列的正确VBA #

这是最贴近你手动操作的方法，核心是在分列时明确告诉Excel你的文本日期格式是日-月-年（xlDMYFormat）：

Sub ConvertTextToDateTime_TextToColumns()
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Worksheets("sheet1")
    
    ' 执行分列，关键是指定第一列为DMY格式的日期
    targetSheet.Range("A2:A10").TextToColumns _
        Destination:=targetSheet.Range("A2"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(Array(1, xlDMYFormat)), ' 匹配dd.mm.yyyy的日-月-年结构
        TrailingMinusNumbers:=True
    
    ' 确保显示格式符合需求
    targetSheet.Range("A2:A10").NumberFormat = "dd.mm.yyyy hh:mm"
End Sub

方案2：用CDate直接转换（适合小数据量） #

如果你的文本日期格式很标准，直接用CDate把文本转成真正的日期值，再设置显示格式就行：

Sub ConvertTextToDateTime_CDate()
    Dim targetSheet As Worksheet
    Dim dataRange As Range
    Dim cell As Range
    
    Set targetSheet = ThisWorkbook.Worksheets("sheet1")
    Set dataRange = targetSheet.Range("A2:A10")
    
    ' 关闭屏幕更新加速运行
    Application.ScreenUpdating = False
    
    For Each cell In dataRange
        ' 先判断是否为有效日期文本
        If IsDate(cell.Value) Then
            cell.Value = CDate(cell.Value) ' 转成Excel日期序列号（真正的日期值）
            cell.NumberFormat = "dd.mm.yyyy hh:mm" ' 设置显示格式
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub

小提示 #

• 如果你的日期格式是月-日-年，把xlDMYFormat换成xlMDYFormat就行。
• 分列方法适合大数据量，速度更快；循环转换适合需要额外逻辑判断的场景。

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