You need to enable JavaScript to run this app. Excel计算双班制工作时长 现有NETWORKDAYS公式结果异常求助
优惠活动
大模型
产品
解决方案
定价
更多

Excel计算双班制工作时长 现有NETWORKDAYS公式结果异常求助

跨天两班制工时计算解决方案

问题根因

你之前的分两段套用原公式的方案存在两个核心错误:

  • 第二班时段跨自然日,原公式默认工作时段均属于同一自然日,upper(2:00AM)的时间值小于lower(9:00PM),时间比较逻辑直接失效,会生成负数结果
  • 原公式未做非负校验,当起止时间落在工作时段外时,计算出的负数值没有被拦截为0,单元格显示为########

公式实现方案

先定义命名区域

提前把固定时间参数定义为命名区域,简化公式:

  • s1:时段1开始时间 11:00 AM,值为11/24
  • e1:时段1结束时间 5:00 PM,值为17/24
  • s2:时段2开始时间 9:00 PM,值为21/24
  • e2:时段2结束时间 次日2:00 AM,值为26/24(用大于1的数值避免跨天判断混乱)
  • daily_work_hours:单日总工时 8小时,值为8/24

完整计算公式

公式中用S代替[@[DC_CREATION_DATE]],E代替[@[ACTUAL_END_DATE]],可直接替换为你的表字段:

=
// 第一部分:完整自然日工时
MAX(0, INT(E)-INT(S)-1) * daily_work_hours
+
// 第二部分:开始日期有效工时
IF(INT(S)=INT(E),
// 起止为同一天的情况
MAX(0, MEDIAN(MOD(S,1),e1,s1) - MEDIAN(MOD(S,1),e1,s1, MOD(E,1)) ) + MAX(0, MEDIAN(IF(MOD(S,1)<2/24,MOD(S,1)+1,MOD(S,1)),e2,s2) - MEDIAN(IF(MOD(S,1)<2/24,MOD(S,1)+1,MOD(S,1)),e2,s2, IF(MOD(E,1)<2/24,MOD(E,1)+1,MOD(E,1))) ),
// 起止非同一天,计算开始日当天工时
MAX(0, e1 - MEDIAN(MOD(S,1),e1,s1)) + MAX(0, e2 - MEDIAN(IF(MOD(S,1)<2/24,MOD(S,1)+1,MOD(S,1)),e2,s2))
)
+
// 第三部分:结束日期有效工时(仅起止非同一天时生效)
IF(INT(S)<>INT(E),
MAX(0, MEDIAN(MOD(E,1),e1,s1) - s1) + MAX(0, MEDIAN(IF(MOD(E,1)<2/24,MOD(E,1)+1,MOD(E,1)),e2,s2) - s2),
0
)

公式计算结果默认单位为天,设置单元格格式为[h]:mm即可显示为小时分钟格式,所有负数值都会被MAX(0,xx)拦截为0,不会再出现########异常。

如果需要保留原NETWORKDAYS.INTL的休息日判断逻辑,把第一部分的MAX(0, INT(E)-INT(S)-1)替换为MAX(0, NETWORKDAYS.INTL(INT(S)+1, INT(E)-1, "0000000"))即可。

可选:VBA自定义函数方案(更易维护)

如果允许使用宏,可以用自定义函数实现,逻辑更清晰,不易出错:

  1. 按Alt+F11打开VBA编辑器,插入模块,粘贴以下代码:
Function CalcWorkHours(startTime As Date, endTime As Date) As Double
    Const s1 = 11 / 24, e1 = 17 / 24, s2 = 21 / 24, e2 = 26 / 24
    Dim curDt As Date, dtStart As Date, dtEnd As Date, totalH As Double
    totalH = 0
    If startTime >= endTime Then CalcWorkHours = 0: Exit Function
    ' 处理跨天的时间偏移
    Function getAdjustedTime(dt As Date) As Double
        Dim t As Double: t = dt - Int(dt)
        If t < 2 / 24 Then t = t + 1
        getAdjustedTime = t
    End Function
    ' 计算完整天
    If Int(endTime) - Int(startTime) > 1 Then
        totalH = totalH + (Int(endTime) - Int(startTime) - 1) * 8 / 24
    End If
    ' 计算开始日
    dtStart = getAdjustedTime(startTime)
    If Int(startTime) = Int(endTime) Then
        dtEnd = getAdjustedTime(endTime)
        totalH = totalH + WorksheetFunction.Max(0, WorksheetFunction.Median(dtStart, e1, s1) - WorksheetFunction.Median(dtEnd, e1, s1))
        totalH = totalH + WorksheetFunction.Max(0, WorksheetFunction.Median(dtStart, e2, s2) - WorksheetFunction.Median(dtEnd, e2, s2))
    Else
        totalH = totalH + WorksheetFunction.Max(0, e1 - WorksheetFunction.Median(dtStart, e1, s1))
        totalH = totalH + WorksheetFunction.Max(0, e2 - WorksheetFunction.Median(dtStart, e2, s2))
        ' 计算结束日
        dtEnd = getAdjustedTime(endTime)
        totalH = totalH + WorksheetFunction.Max(0, WorksheetFunction.Median(dtEnd, e1, s1) - s1)
        totalH = totalH + WorksheetFunction.Max(0, WorksheetFunction.Median(dtEnd, e2, s2) - s2)
    End If
    CalcWorkHours = totalH
End Function
  1. 回到表格直接使用公式=CalcWorkHours([@[DC_CREATION_DATE]],[@[ACTUAL_END_DATE]])即可得到结果。

内容的提问来源于stack exchange,提问作者Major Aly

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.17 11:54:03