VBA工时计算函数bug求助：周四起始日计算结果异常

问题根源 #

你的代码中工作日判断逻辑错误：
使用Weekday(currentDate, vbSunday) <=4时，vbSunday作为参数的情况下，Weekday返回值对应关系为：

• 1 = 周日
• 2 = 周一
• 3 = 周二
• 4 = 周三
• 5 = 周四
• 6 = 周五
• 7 = 周六

这就导致周四（返回值5）被判定为非工作日，直接跳过工时计算，所以起始日为周四时会出现异常。

另外注意：你描述的工作时段是8:30-16:30，但代码里定义的是workStart = TimeValue("08:00:00")和workEnd = TimeValue("16:00:00")，这也和需求不符，建议同步修正。

修复后的代码 #

Function CountWorkHours(start_date As Date, end_date As Date, holidays As Range) As Double
    Dim totalHours As Double
    Dim currentDate As Date
    Dim workStart As Date
    Dim workEnd As Date
    Dim isHoliday As Boolean
    Dim holiday As Variant
    Dim startHour As Date
    Dim endHour As Date

    ' 修正工作时段为需求的8:30-16:30
    workStart = TimeValue("08:30:00")
    workEnd = TimeValue("16:30:00")

    totalHours = 0
    currentDate = Int(start_date) ' 仅取日期部分开始遍历

    ' 遍历起始日到结束日的每一天
    Do While currentDate <= Int(end_date)
        isHoliday = False
        For Each holiday In holidays
            If currentDate = Int(holiday) Then
                isHoliday = True
                Exit For
            End If
        Next holiday
       
        ' 修正工作日判断：周日到周四（排除周五、周六）且非节假日
        If (Weekday(currentDate, vbSunday) >= 1 And Weekday(currentDate, vbSunday) <= 5) And Not isHoliday Then
            ' 确定当天的工时起始时间
            If currentDate = Int(start_date) Then
                startHour = TimeValue(start_date)
                If startHour < workStart Then startHour = workStart
            Else
                startHour = workStart
            End If

            ' 确定当天的工时结束时间
            If currentDate = Int(end_date) Then
                endHour = TimeValue(end_date)
                If endHour > workEnd Then endHour = workEnd
            Else
                endHour = workEnd
            End If

            ' 累加当天有效工时
            If endHour > startHour Then
                totalHours = totalHours + (endHour - startHour) * 24
            End If
        End If
        currentDate = currentDate + 1
    Loop

    CountWorkHours = totalHours
End Function

关键修正点 #

• 修正工作日判断条件为Weekday(currentDate, vbSunday) >=1 And Weekday(currentDate, vbSunday) <=5，确保周四（返回值5）被纳入工作日范围
• 同步将工作时段调整为你需求的8:30-16:30，和描述一致

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