Excel计算双班制工作时长 现有NETWORKDAYS公式结果异常求助
跨天两班制工时计算解决方案
问题根因
你之前的分两段套用原公式的方案存在两个核心错误:
- 第二班时段跨自然日,原公式默认工作时段均属于同一自然日,upper(2:00AM)的时间值小于lower(9:00PM),时间比较逻辑直接失效,会生成负数结果
- 原公式未做非负校验,当起止时间落在工作时段外时,计算出的负数值没有被拦截为0,单元格显示为
########
公式实现方案
先定义命名区域
提前把固定时间参数定义为命名区域,简化公式:
s1:时段1开始时间 11:00 AM,值为11/24e1:时段1结束时间 5:00 PM,值为17/24s2:时段2开始时间 9:00 PM,值为21/24e2:时段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自定义函数方案(更易维护)
如果允许使用宏,可以用自定义函数实现,逻辑更清晰,不易出错:
- 按
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
- 回到表格直接使用公式
=CalcWorkHours([@[DC_CREATION_DATE]],[@[ACTUAL_END_DATE]])即可得到结果。
内容的提问来源于stack exchange,提问作者Major Aly
相关产品推荐
相关产品推荐

