Excel按分段工作规则计算两日期之间工单处理有效工时

需求场景 #

计算工单创建时间、解决时间两个节点之间，落在指定工作时段内的有效时长，自动排除周末、法定节假日、午休等非工作时间，支持函数公式、VBA两类实现方式。

校验示例 #

按以下规则计算结果应为3小时：

• 工单创建时间：2022年6月3日（周五）16:00
• 工单解决时间：2022年6月6日（周一）10:00
• 工作时段：上午9:00-12:00，下午13:30-18:00

方案1：原生函数公式实现 #

前置配置 #

先在工作簿内配置固定参数：

• 定义名称PublicHolidays，区域内录入所有法定节假日日期
• 新建Working_Hours工作表，固定单元格值：
  • B2：上午上班时间 9:00
  • B3：上午下班时间 12:00
  • E2：下午上班时间 13:30
  • E3：下午下班时间 18:00
• 原始数据中，工单创建时间存于D列，解决时间存于E列，数据从第2行开始。

可直接复用的公式 #

你当前使用的公式逻辑成立，仅需注意不同地区Excel版本的公式参数分隔符可能为分号，以下为逗号分隔版（结果直接返回小时数）：

=IF(
 (NETWORKDAYS(D2,E2,PublicHolidays)-1)*(Working_Hours!$B$3-Working_Hours!$B$2)
 +IF(NETWORKDAYS(D2,E2,PublicHolidays),MEDIAN(MOD(E2,1),Working_Hours!$B$3,Working_Hours!$B$2),Working_Hours!$B$3)
 -MEDIAN(NETWORKDAYS(D2,E2,PublicHolidays)*MOD(D2,1),Working_Hours!$B$3,Working_Hours!$B$2)
 +(NETWORKDAYS(D2,E2,PublicHolidays)-1)*(Working_Hours!$E$3-Working_Hours!$E$2)
 +IF(NETWORKDAYS(D2,E2,PublicHolidays),MEDIAN(MOD(E2,1),Working_Hours!$E$3,Working_Hours!$E$2),Working_Hours!$E$3)
 -MEDIAN(NETWORKDAYS(D2,E2,PublicHolidays)*MOD(D2,1),Working_Hours!$E$3,Working_Hours!$E$2)
 <0,
 0,
 (NETWORKDAYS(D2,E2,PublicHolidays)-1)*(Working_Hours!$B$3-Working_Hours!$B$2)
 +IF(NETWORKDAYS(D2,E2,PublicHolidays),MEDIAN(MOD(E2,1),Working_Hours!$B$3,Working_Hours!$B$2),Working_Hours!$B$3)
 -MEDIAN(NETWORKDAYS(D2,E2,PublicHolidays)*MOD(D2,1),Working_Hours!$B$3,Working_Hours!$B$2)
 +(NETWORKDAYS(D2,E2,PublicHolidays)-1)*(Working_Hours!$E$3-Working_Hours!$E$2)
 +IF(NETWORKDAYS(D2,E2,PublicHolidays),MEDIAN(MOD(E2,1),Working_Hours!$E$3,Working_Hours!$E$2),Working_Hours!$E$3)
 -MEDIAN(NETWORKDAYS(D2,E2,PublicHolidays)*MOD(D2,1),Working_Hours!$E$3,Working_Hours!$E$2)
)*24

逻辑说明

• 将上午、下午两个工作时段拆分独立计算，避免午休时长被误统计
• 用NETWORKDAYS自动剔除周末、PublicHolidays区域内的法定节假日
• 首尾两个非完整工作日，用MEDIAN取时间交集计算有效时长；中间跨的完整工作日直接按单日7.5小时工作时长累加
• 增加负值判断，若解决时间早于创建时间直接返回0，过滤异常数据。
  代入校验示例计算：周五16:00-18:00计2小时，周末不计，周一9:00-10:00计1小时，总时长3小时，符合预期。

方案2：VBA自定义函数实现 #

适合需要灵活调整排班规则、数据量较大的场景，按Alt+F11打开VBA编辑器，插入新模块后粘贴以下代码，即可在单元格内直接调用=CalcWorkHours(D2,E2,节假日区域)计算时长。

Function CalcWorkHours(StartTime As Date, EndTime As Date, Optional HolidayRng As Range = Nothing) As Double
    ' 工作时段常量，可按需修改
    Const AM_START As Double = 9 / 24
    Const AM_END As Double = 12 / 24
    Const PM_START As Double = 13.5 / 24
    Const PM_END As Double = 18 / 24
    
    Dim curDate As Date, totalHours As Double
    totalHours = 0
    
    ' 异常值拦截
    If EndTime < StartTime Then
        CalcWorkHours = 0
        Exit Function
    End If
    
    ' 逐天遍历计算有效时长
    For curDate = Int(StartTime) To Int(EndTime)
        ' 判断当日是否为工作日
        Dim isWorkday As Boolean
        isWorkday = True
        If Weekday(curDate, vbMonday) > 5 Then isWorkday = False
        If Not HolidayRng Is Nothing Then
            If Application.CountIf(HolidayRng, curDate) > 0 Then isWorkday = False
        End If
        If Not isWorkday Then GoTo NextDayLoop
        
        ' 取当日实际计算的起止时间
        Dim dayStart As Date, dayEnd As Date
        dayStart = IIf(Int(StartTime) = curDate, StartTime, curDate + AM_START)
        dayEnd = IIf(Int(EndTime) = curDate, EndTime, curDate + PM_END)
        
        ' 计算上午时段重叠时长
        If dayStart < curDate + AM_END And dayEnd > curDate + AM_START Then
            totalHours = totalHours + (Min(dayEnd, curDate + AM_END) - Max(dayStart, curDate + AM_START)) * 24
        End If
        
        ' 计算下午时段重叠时长
        If dayStart < curDate + PM_END And dayEnd > curDate + PM_START Then
            totalHours = totalHours + (Min(dayEnd, curDate + PM_END) - Max(dayStart, curDate + PM_START)) * 24
        End If
NextDayLoop:
    Next curDate
    CalcWorkHours = totalHours
End Function

' 辅助比较函数
Private Function Max(a As Variant, b As Variant) As Variant
    Max = IIf(a > b, a, b)
End Function
Private Function Min(a As Variant, b As Variant) As Variant
    Min = IIf(a < b, a, b)
End Function

方案优势 #

• 逻辑直观，调整工作时段仅需修改顶部常量值即可，不需要重写复杂公式
• 逐天遍历的逻辑不存在长公式嵌套的性能问题，适配万行以上级别的数据计算
• 调用时可直接选择节假日区域，不需要提前定义全局名称，使用更灵活。

内容的提问来源于stack exchange，提问作者Salah K.