CSV导入Excel时YYYYMMDD格式日期转换错误问题排查与修复

问题描述 #

待导入的CSV包含A-D列，仅需导入A-C列，该部分操作正常，但日期格式转换异常。CSV中日期为YYYYMMDD格式，需转换为dd/mm/yyyy格式（系统区域设置为此格式），但运行代码后日期被错误转换：例如2023年12月4日应显示为04/12/2023，却被转为12/04/2023（即2023年4月12日）。

原VBA代码 #

Private Sub ImprtBtnABSA_Click()
    Dim vFile, arIn, arOut()
    Dim wbCSV As Workbook
    Dim i As Long, lastRow As Long, s As String
    Dim t0 As Single

    'Select a text file through the file dialog.
    'Get the path and file name of the selected file to the variable.
    vFile = Application.GetOpenFilename("ExcelFile *.txt,*.txt;*.csv", _
        Title:="Select CSV file", MultiSelect:=False)

    'If you don't select a file, exit sub.
    If TypeName(vFile) = "Boolean" Then
        Application.ScreenUpdating = True
        Exit Sub
    End If
    t0 = Timer

    'The selected text file is imported into an Excel file. format:2 is csv, format:1 is tab
    Set wbCSV = Workbooks.Open(Filename:=vFile, Format:=2, ReadOnly:=True)

    'Bring all the contents of the sheet into an array
    'and close the text file
    With wbCSV.Sheets(1)
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lastRow < 2 Then Exit Sub ' If last row is less than 2, exit sub
        ' Exclude the last row by adjusting the range
        arIn = .Range("A2:C" & lastRow).Value
        
        wbCSV.Close
    End With
    
    'built output array from input array
    ReDim Preserve arOut(1 To UBound(arIn), 1 To 3)
    For i = 1 To UBound(arIn, 1)
        ' Adjust column numbers as needed
        s = Trim(arIn(i, 2))
        ' Remove text within parentheses including brackets
        s = RemoveTextInParentheses(s)
        ' Assuming the date is in the format YYYYMMDD
        arOut(i, 1) = Format(DateSerial(Left(arIn(i, 1), 4), Mid(arIn(i, 1), 5, 2), Right(arIn(i, 1), 2)), "dd/mm/yyyy")
        arOut(i, 2) = s ' Trimmed and without text within parentheses
        arOut(i, 3) = arIn(i, 3) ' Column C
    Next

    'write output array to sheet2
    With ThisWorkbook.Sheets(2)
        .UsedRange.Clear
        .Range("A1:C1") = Array("Date", "Description", "Amount")
        .Range("A:A").NumberFormat = "dd/mm/yyyy"
        .Range("B:B").NumberFormat = "@"
        .Range("C:C").NumberFormat = "General"
        .Range("A2").Resize(UBound(arOut), 3).Value = arOut
        .Columns("A:C").AutoFit
    End With
    MsgBox "Done", vbInformation, Format(Timer - t0, "0.0 secs")
End Sub

错误原因 #

核心问题是格式化字符串被Excel自动解析时的格式冲突：

• 代码中用Format(DateSerial(...), "dd/mm/yyyy")将日期转换为字符串，写入Excel时，尽管已设置单元格格式为dd/mm/yyyy，但Excel会根据系统区域的默认解析规则，将dd/mm/yyyy格式的字符串误识别为mm/dd/yyyy（部分区域默认优先识别月在前的格式），导致日和月颠倒。
• 格式化后的字符串本质是文本，Excel的自动解析逻辑会覆盖单元格的格式设置。

修复方案 #

方案1：写入日期序列号（推荐） #

直接生成Excel可识别的日期值（内部存储为序列号），依赖已设置的单元格格式显示正确的dd/mm/yyyy：
修改循环中的日期处理代码，去掉Format函数，直接赋值DateSerial生成的日期值：

' 替换原日期处理行
arOut(i, 1) = DateSerial(Left(arIn(i, 1), 4), Mid(arIn(i, 1), 5, 2), Right(arIn(i, 1), 2))

此时写入的是真正的日期类型数据，Excel会严格按照单元格设置的dd/mm/yyyy格式显示，不会出现解析颠倒的问题，同时日期还能参与后续的计算和筛选。

方案2：强制以文本格式写入（不推荐） #

如果需要保留纯文本格式的日期（无法参与日期计算），可以通过两种方式实现：

1. 提前设置单元格为文本格式：
   在写入数据前，将A列设置为文本格式，再写入格式化后的字符串：

With ThisWorkbook.Sheets(2)
    .UsedRange.Clear
    .Range("A1:C1") = Array("Date", "Description", "Amount")
    .Range("A:A").NumberFormat = "@" ' 先设置为文本格式
    .Range("B:B").NumberFormat = "@"
    .Range("C:C").NumberFormat = "General"
    ' 循环中保持原Format代码
    arOut(i, 1) = Format(DateSerial(Left(arIn(i, 1), 4), Mid(arIn(i, 1), 5, 2), Right(arIn(i, 1), 2)), "dd/mm/yyyy")
    .Range("A2").Resize(UBound(arOut), 3).Value = arOut
    .Columns("A:C").AutoFit
End With

2. 添加单引号强制文本：
   在格式化后的日期字符串前添加单引号，强制Excel将其识别为文本：

' 替换原日期处理行
arOut(i, 1) = "'" & Format(DateSerial(Left(arIn(i, 1), 4), Mid(arIn(i, 1), 5, 2), Right(arIn(i, 1), 2)), "dd/mm/yyyy")

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