使用VBA执行文本分列时日期格式从DMY变为MDY的问题求助

核心问题分析 #

你的代码里TextToColumns的FieldInfo参数中，第三列误用了4（对应xlYMDFormat），且VBA文本分列默认跟随系统区域设置解析日期，导致DMY格式被误判为MDY。后续的CDate方法同样受系统区域影响，无法强制按DMY逻辑转换。

修正方案 #

1. 强制文本分列按DMY解析日期 #

先将目标列设为文本格式避免Excel自动转换，再修改FieldInfo参数，给第三列指定xlDMYFormat（对应枚举值3）：

' 先将C列设为文本格式，防止自动转日期
Columns("C:C").NumberFormat = "@"

' 执行文本分列，第三列强制按DMY解析
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 3), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True

注：xlDMYFormat对应值为3，xlMDYFormat为2，xlYMDFormat为4，必须明确指定3来强制按日/月/年解析。

2. 安全转换为日期格式（不受系统区域干扰） #

不要用CDate，改用DateSerial手动拆分日期字符串并构造日期，确保转换逻辑完全可控：

Dim lastRow As Long
Dim v As Variant, j As Long
Dim dateParts As Variant

lastRow = Range("C" & Rows.Count).End(xlUp).Row
If lastRow >= 2 Then
    v = Range("C2:C" & lastRow).Value
    For j = 1 To UBound(v)
        If v(j, 1) <> "" Then
            ' 按斜杠拆分日、月、年部分
            dateParts = Split(v(j, 1), "/")
            If UBound(dateParts) = 2 Then
                ' 强制按DMY组合日期
                v(j, 1) = DateSerial(CLng(dateParts(2)), CLng(dateParts(1)), CLng(dateParts(0)))
            End If
        End If
    Next j
    ' 写入转换后的值并设置DMY显示格式
    With Range("C2").Resize(UBound(v))
        .Value = v
        .NumberFormat = "dd/mm/yyyy"
    End With
End If

完整修正代码 #

Sub SplitTextAndFixDMYDate()
    ' 先将C列设为文本格式，防止Excel自动转换日期
    Columns("C:C").NumberFormat = "@"

    ' 执行文本分列，第三列强制按DMY解析
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 3), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True

    ' 将C列文本日期转换为真实日期，设置显示格式为dd/mm/yyyy
    Dim lastRow As Long
    Dim v As Variant, j As Long
    Dim dateParts As Variant

    lastRow = Range("C" & Rows.Count).End(xlUp).Row
    If lastRow >= 2 Then
        v = Range("C2:C" & lastRow).Value
        For j = 1 To UBound(v)
            If v(j, 1) <> "" Then
                dateParts = Split(v(j, 1), "/")
                If UBound(dateParts) = 2 Then
                    On Error Resume Next ' 忽略非法日期值
                    v(j, 1) = DateSerial(CLng(dateParts(2)), CLng(dateParts(1)), CLng(dateParts(0)))
                    On Error GoTo 0
                End If
            End If
        Next j
        With Range("C2").Resize(UBound(v))
            .Value = v
            .NumberFormat = "dd/mm/yyyy"
        End With
    End If
End Sub

关键说明 #

• 必须先设置目标列为文本格式，避免Excel在分列前就自动篡改日期格式。
• FieldInfo中的3是强制DMY解析的核心，不要依赖默认的xlGeneral或错误的格式枚举值。
• 用DateSerial手动构造日期是最可靠的方式，完全规避系统区域设置的影响。

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