VBA日期拆分宏异常：日≤12时误将日识别为月的问题排查

问题根源 #

你遇到的问题核心是系统区域日期格式的差异：DateValue函数会根据用户电脑的区域设置解析日期，比如部分同事的系统采用MM/DD/YYYY格式，当日期中的日≤12时，函数会错误地把日当成月份解析。另外，你添加的.NumberFormat只是修改单元格的显示格式，不会改变单元格内容的实际存储类型——如果是文本日期，这个设置完全无效。

解决方案 #

根据你的日期列存储类型，分两种情况处理：

情况1：日期列是真正的日期值（不是文本）

Excel中日期本质是数字序列号，直接用Month()和Year()提取即可，彻底规避区域格式问题：

sh1.Range("AB1:AE1") = Array("MM", "YYYY", "MM_mod", "YYYY_mod")

Dim lastRow As Long
lastRow = sh1.Cells(sh1.Rows.Count, "H").End(xlUp).Row

' 合并循环，提升效率
Dim i As Long
For i = 2 To lastRow
    ' 注意原代码里的Range("i" & i)是小写i，应该是大写I
    sh1.Range("AB" & i) = Month(sh1.Range("I" & i).Value)
    sh1.Range("AC" & i) = Year(sh1.Range("I" & i).Value)
    sh1.Range("AD" & i) = Month(sh1.Range("J" & i).Value)
    sh1.Range("AE" & i) = Year(sh1.Range("J" & i).Value)
Next i

情况2：日期列是文本格式的日期（比如存储为"12-05-2023"这类字符串）

如果单元格是文本，必须明确指定日期格式来解析，避免系统自动歧义。可以用Split拆分字符串，手动提取月和年：

sh1.Range("AB1:AE1") = Array("MM", "YYYY", "MM_mod", "YYYY_mod")

Dim lastRow As Long
lastRow = sh1.Cells(sh1.Rows.Count, "H").End(xlUp).Row

Dim i As Long, dateParts As Variant
For i = 2 To lastRow
    ' 处理I列文本日期，格式为d-m-yyyy h:mm
    dateParts = Split(Left(sh1.Range("I" & i).Value, InStr(sh1.Range("I" & i).Value, " ") - 1), "-")
    sh1.Range("AB" & i) = dateParts(1) ' 提取月份（d-m-yyyy中第二个部分是月）
    sh1.Range("AC" & i) = dateParts(2) ' 提取年份
    
    ' 处理J列文本日期
    dateParts = Split(Left(sh1.Range("J" & i).Value, InStr(sh1.Range("J" & i).Value, " ") - 1), "-")
    sh1.Range("AD" & i) = dateParts(1)
    sh1.Range("AE" & i) = dateParts(2)
Next i

额外优化点 #

• 原代码中Range("i" & i)是小写字母i，属于语法错误，会导致引用错误的单元格；
• 把4个独立循环合并成1个，大幅提升代码运行效率；
• 统一使用sh1.前缀限定工作表，避免默认工作表切换导致的错误。

内容的提问来源于stack exchange，提问作者Antonio La Mura