Excel VBA数据传输中日/月互换问题（日≤12时）

问题核心 #

使用VBA转置传输带时间的日期数据时，当日份≤12时，目标单元格会出现日月互换的情况，即便源/目标单元格格式均设为dd/mm/yyyy hh:mm，且系统和Excel的日期设置正确。

问题原因 #

直接通过.Value属性赋值时，VBA会默认按美式日期（mm/dd）规则解析那些看起来符合格式的日期值——即便是NOW()生成的日期序列值，转置过程中也可能被错误解析为美式日期，导致日月颠倒。

解决方案 #

方案1：用复制粘贴替代直接赋值（最稳妥）

通过Copy和PasteSpecial直接复制源数据的所有属性（包括值、格式），避免VBA的日期解析干扰：

Sub TransferDataAndSort()
    Dim srcSheet As Worksheet
    Dim destSheet As Worksheet
    Dim lastRow As Long
    
    ' 指定源表和目标表
    Set srcSheet = ThisWorkbook.Sheets("Input Data")
    Set destSheet = ThisWorkbook.Sheets("Raw Data")
    
    ' 获取目标表下一个空行（以B列为例）
    lastRow = destSheet.Cells(destSheet.Rows.Count, "B").End(xlUp).Row + 1
    
    ' 复制源区域并转置粘贴
    srcSheet.Range("C2:C6").Copy
    destSheet.Range("B" & lastRow).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    Application.CutCopyMode = False ' 清除复制状态
End Sub

方案2：直接操作日期序列值

Excel的日期本质是代表时间的数字序列，直接读取这个数值再赋值，跳过VBA的日期解析步骤：

Sub TransferDataAndSort()
    Dim srcSheet As Worksheet
    Dim destSheet As Worksheet
    Dim lastRow As Long
    Dim dateSeqValue As Double
    
    Set srcSheet = ThisWorkbook.Sheets("Input Data")
    Set destSheet = ThisWorkbook.Sheets("Raw Data")
    
    lastRow = destSheet.Cells(destSheet.Rows.Count, "B").End(xlUp).Row + 1
    
    ' 转置赋值普通数据
    destSheet.Range("B" & lastRow & ":E" & lastRow).Value = Application.Transpose(srcSheet.Range("C2:C5").Value)
    ' 直接读取日期的序列值并赋值
    dateSeqValue = srcSheet.Range("C6").Value
    destSheet.Range("F" & lastRow).Value = dateSeqValue
    ' 确保目标单元格格式正确
    destSheet.Range("F" & lastRow).NumberFormat = "dd/mm/yyyy hh:mm"
End Sub

方案3：先设置目标区域格式再赋值

先为目标单元格指定正确的日期格式，再进行赋值操作：

Sub TransferDataAndSort()
    Dim srcSheet As Worksheet
    Dim destSheet As Worksheet
    Dim lastRow As Long
    
    Set srcSheet = ThisWorkbook.Sheets("Input Data")
    Set destSheet = ThisWorkbook.Sheets("Raw Data")
    
    lastRow = destSheet.Cells(destSheet.Rows.Count, "B").End(xlUp).Row + 1
    
    ' 预先设置目标区域的日期格式
    destSheet.Range("B" & lastRow & ":F" & lastRow).NumberFormat = "dd/mm/yyyy hh:mm"
    ' 转置赋值数据
    destSheet.Range("B" & lastRow & ":F" & lastRow).Value = Application.Transpose(srcSheet.Range("C2:C6").Value)
End Sub

关键提示 #

优先使用方案1，复制粘贴能完整保留源数据的所有属性，彻底避免日期解析错误；如果因需求限制不能用复制粘贴，方案2是更可靠的选择，直接操作日期序列值不会出现格式混淆问题。

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