为何VBA中Worksheet SUMIFS函数对部分日期返回零，部分正常？

我们需要遍历约500条数据，识别归属Rate A和Rate B的条目并累加工时，同时支持设置任意数量的费率周期——将2018至2021年的总周期划分为多个时段，第一个时段早于数据起始时间，最后一个时段覆盖到数据结束时间。

费率周期对话框使用DTPicker控件，系统采用英国日期格式（dd/mm/yyyy），原本以为用DTPicker和DateSerial不会有问题，但遇到以下异常：

• 仅设置1个费率周期时，总计计算正确；
• 设置2个费率周期且输入日期为01/01/2020时，第一个周期总计看似正确，但第二个周期结果低于预期；
• 设置2个费率周期且输入日部分大于12的日期（如13/01/2020）时，两个周期的Rate A和Rate B总计均为0；
• 边缘情况：当周期结束日期为12/01/2020时，所有数据都被判定为早于该日期（实际数据持续到2021年1月，包含大量Rate A/B条目）。

循环前后的调试消息显示SumIfs使用的日期是正确的，但计算结果异常。

相关代码 #

Public rateAHours() As Single
Public rateBHours() As Single

Sub DebugSumRateData()

'Define ranges for Work Hours (sumRange), column with rate data (A, B or otherwise) and column with date of work
Dim sumRange As Range
Dim rateRange As Range
Dim dateRange As Range

Dim periodStartDate As Date
Dim periodEndDate As Date

Set dateRange = Range("Data!B:B")
Set rateRange = Range("Data!E:E")
Set sumRange = Range("Data!F:F")

'Setup dates for rate period
numberOfRatePeriods = InputBox("How many rate periods apply to this schedule?", "Number of Rate Periods")

'Set all arrays to the size required for the number of rate periods
ReDim endDates(numberOfRatePeriods) As Date
ReDim rateAHours(numberOfRatePeriods) As Single
ReDim rateBHours(numberOfRatePeriods) As Single

If (numberOfRatePeriods > 1) Then
    For i = 1 To numberOfRatePeriods - 1
        RatePeriodDialog.DatePromptLabel = "Please enter end date of rate period " & i
        RatePeriodDialog.Show
        endDates(i) = ratePeriodInputDate
    Next i
End If

'Final rate period is until end of time (or near enough)
endDates(numberOfRatePeriods) = DateSerial(9999, 1, 1)

periodStartDate = DateSerial(1900, 1, 1)

For i = 1 To UBound(endDates)
    periodEndDate = endDates(i)

    MsgBox "Start of loop " & i & " - Start Date is " & periodStartDate & " End Date is " & periodEndDate

    rateAHours(i) = WorksheetFunction.SumIfs(sumRange, rateRange, "A", dateRange, ">=" & periodStartDate, dateRange, "<=" & periodEndDate)
    rateBHours(i) = WorksheetFunction.SumIfs(sumRange, rateRange, "B", dateRange, ">=" & periodStartDate, dateRange, "<=" & periodEndDate)

    periodStartDate = DateAdd("d", 1, periodEndDate)

    MsgBox "End of loop " & i & " - Start Date is " & periodStartDate & " End Date is " & periodEndDate

    'Debug Message Box - shows all rate totals for this loop
    MsgBox rateAHours(i) & vbCrLf & _
    rateBHours(i) & vbCrLf

Next i

End Sub

具体现象细节 #

• 1个费率周期：Rate A和Rate B的工时计算正确，调试框显示75.5和7.2；
• 2个费率周期（结束日期01/01/2020）：第一个循环显示49和7.1，第二个循环显示22.3和0.1，存在数据缺失；
• 2个费率周期（结束日期13/01/2020）：两个循环的总计均为0；
• 2个费率周期（结束日期12/01/2020）：第一个循环显示全部数据75.5和7.2，第二个循环为0，但实际数据持续到2021年1月。

问题核心是VBA日期转字符串时的格式冲突：
当你把Date类型变量和字符串（如">="）拼接时，VBA会按系统短日期格式转字符串，但Excel的SumIfs函数默认按美式日期格式（mm/dd/yyyy）解析，导致格式不匹配：

• 13/01/2020会被解析为美式的13月1日（无效日期），因此SumIfs无法匹配任何数据，返回0；
• 12/01/2020被解析为美式的12月1日，覆盖了所有到2021年1月的数据，导致第二个周期无数据。

修复方法1：使用日期序列号传递参数 #

Excel内部用序列号存储日期，直接传递日期的数值给SumIfs，避免格式解析问题：

rateAHours(i) = WorksheetFunction.SumIfs(sumRange, _
    rateRange, "A", _
    dateRange, ">=" & CDbl(periodStartDate), _
    dateRange, "<=" & CDbl(periodEndDate))
rateBHours(i) = WorksheetFunction.SumIfs(sumRange, _
    rateRange, "B", _
    dateRange, ">=" & CDbl(periodStartDate), _
    dateRange, "<=" & CDbl(periodEndDate))

修复方法2：强制转为美式格式字符串 #

把日期按mm/dd/yyyy格式转成字符串，确保Excel能正确解析：

Dim startDateStr As String
Dim endDateStr As String
startDateStr = Format(periodStartDate, "mm/dd/yyyy")
endDateStr = Format(periodEndDate, "mm/dd/yyyy")

rateAHours(i) = WorksheetFunction.SumIfs(sumRange, _
    rateRange, "A", _
    dateRange, ">=" & startDateStr, _
    dateRange, "<=" & endDateStr)
rateBHours(i) = WorksheetFunction.SumIfs(sumRange, _
    rateRange, "B", _
    dateRange, ">=" & startDateStr, _
    dateRange, "<=" & endDateStr)

额外优化建议 #

• 避免整列引用（Range("Data!B:B")），改为引用实际数据区域，比如Range("Data!B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)，提升性能；
• 在模块顶部添加Option Explicit，强制声明所有变量，避免未声明变量导致的潜在问题；
• 用Debug.Print代替MsgBox调试，减少弹窗干扰，更高效查看日志。

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