如何在VBA中将日期格式从mm/dd/yyyy改为dd/mm/yyyy？（D列部分单元格异常）

尝试将D列日期格式从mm/dd/yyyy改为dd/mm/yyyy，但仅D2至D7单元格格式异常，列内其余单元格正常。执行代码Columns("D").NumberFormatLocal = "dd/mm/yyyy"未生效。

代码逻辑：从主数据工作表“All”提取Sheet1的J6单元格指定员工的打卡日期与时间到Sheet1的A、B列；通过Scripting Dictionary获取每日最早/最晚打卡时间，输出到D、E、F列，D列日期格式出现异常。

截图说明：D列部分单元格显示为mm/dd/yyyy格式（如09/05/2024实际应为05/09/2024），其余单元格为正确的dd/mm/yyyy格式。

• 通过Application.Transpose(oDicMin.keys)写入单元格时，部分日期被Excel识别为文本型日期，导致格式设置无法覆盖原有显示
• Scripting Dictionary存储日期时，可能因数据类型转换问题，输出时变成字符串而非真正的日期值

关键修改步骤 #

1. 提前设置列格式：在写入数据前先配置D列的日期格式，确保Excel以指定格式解析后续写入的日期
2. 强制转换数据类型：将Dictionary的Keys逐个转换为Date类型后再写入，避免文本格式冲突

修改后的完整代码 #

Dim oDicMin As Object, oDicMax As Object, rngData As Range
Dim i As Long, vKey, vTmp
Dim arrData
Dim staffPsn As String
Columns("A:B").ClearContents
Range("C2:F50").ClearContents
Range("L2:P22").ClearContents

staffLR = Sheets("All").Cells(Rows.Count, 1).End(xlUp).Row

staffPsn = Sheet1.Range("j6")

counter = 1

For x = 5 To staffLR
 If staffPsn = Sheets("All").Cells(x, 5) Then
    Sheet1.Cells(counter, 1) = CDate(Sheets("All").Cells(x, 1))
    Sheet1.Cells(counter, 2) = CDate(Sheets("All").Cells(x, 3))
    counter = counter + 1
 End If

Next x

Set oDicMin = CreateObject("scripting.dictionary")
Set oDicMax = CreateObject("scripting.dictionary")
' load data into an array
Set rngData = Range("A1").CurrentRegion
arrData = rngData.Value
' loop through data
For i = LBound(arrData) To UBound(arrData)
    vKey = arrData(i, 1) ' Date (Col A) as the key
    vTmp = arrData(i, 2)
    If oDicMin.exists(vKey) Then
        If vTmp < oDicMin(vKey) Then
            oDicMin(vKey) = vTmp ' update Min
        End If
        If vTmp > oDicMax(vKey) Then
            oDicMax(vKey) = vTmp ' update Max
        End If
    Else ' Init. min and max
        oDicMin(vKey) = vTmp
        oDicMax(vKey) = vTmp
    End If
Next i
' header of output
Range("D1:F1").Value = Array("Date", "Clock-In", "Clock-Out")
Columns("E:F").NumberFormatLocal = "[h]:mm:ss"
Columns("C").NumberFormatLocal = "dd/mm/yyyy"

' 提前设置D列日期格式
Columns("D").NumberFormatLocal = "dd/mm/yyyy"

Dim iCnt As Long
iCnt = oDicMin.Count
' 转换Dictionary的Keys为Date类型后写入，避免文本格式问题
Dim dateKeys() As Date
ReDim dateKeys(1 To iCnt)
Dim idx As Long
idx = 1
For Each vKey In oDicMin.keys
    dateKeys(idx) = CDate(vKey)
    idx = idx + 1
Next vKey
Range("D2").Resize(iCnt, 1) = Application.Transpose(dateKeys)
Range("E2").Resize(iCnt, 1) = Application.Transpose(oDicMin.items)
Range("F2").Resize(iCnt, 1) = Application.Transpose(oDicMax.items)

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