计算两个日期间的工作日时长（分钟）：Excel公式需求

问题背景 #

现有服务工单系统记录工单创建时间（G列）与关闭时间（J列），需在P列编写Excel公式，计算两时间间的有效工作分钟数。工作时间为周一至周五7:00-16:00，若工单在周五16点后、周六或周日创建，统计起始时间为下一个工作日7:00。已尝试NETWORKDAYS、NETWORKDAYS.INTL函数，但结果与手动计算值不匹配，P列需保持默认数字格式，无需转换为h:mm格式。

最终公式 #

在P2单元格输入以下公式，下拉填充即可：

=MAX(0,
    (NETWORKDAYS.INTL(
        IF(AND(WEEKDAY(G2,2)<=5,G2>=INT(G2)+TIME(7,0,0),G2<=INT(G2)+TIME(16,0,0)),G2,WORKDAY.INTL(G2,0,"0000011")+TIME(7,0,0)),
        J2,
        "0000011"
    )-1)*540
    +MAX(0,MIN(J2,INT(J2)+TIME(16,0,0))-MAX(IF(AND(WEEKDAY(G2,2)<=5,G2>=INT(G2)+TIME(7,0,0),G2<=INT(G2)+TIME(16,0,0)),G2,WORKDAY.INTL(G2,0,"0000011")+TIME(7,0,0)),INT(J2)+TIME(7,0,0)))
    +MAX(0,MIN(INT(IF(AND(WEEKDAY(G2,2)<=5,G2>=INT(G2)+TIME(7,0,0),G2<=INT(G2)+TIME(16,0,0)),G2,WORKDAY.INTL(G2,0,"0000011")+TIME(7,0,0)))+TIME(16,0,0),J2)-MAX(IF(AND(WEEKDAY(G2,2)<=5,G2>=INT(G2)+TIME(7,0,0),G2<=INT(G2)+TIME(16,0,0)),G2,WORKDAY.INTL(G2,0,"0000011")+TIME(7,0,0)),INT(IF(AND(WEEKDAY(G2,2)<=5,G2>=INT(G2)+TIME(7,0,0),G2<=INT(G2)+TIME(16,0,0)),G2,WORKDAY.INTL(G2,0,"0000011")+TIME(7,0,0)))+TIME(7,0,0)))
)*1440

公式拆解说明 #

1. 自动修正起始时间 #

IF(AND(WEEKDAY(G2,2)<=5,G2>=INT(G2)+TIME(7,0,0),G2<=INT(G2)+TIME(16,0,0)),G2,WORKDAY.INTL(G2,0,"0000011")+TIME(7,0,0))

• 先判断创建时间是否在周一至周五7:00-16:00范围内：
  • WEEKDAY(G2,2)<=5：确认是周一到周五
  • G2>=INT(G2)+TIME(7,0,0)：时间不早于当日7点
  • G2<=INT(G2)+TIME(16,0,0)：时间不晚于当日16点
• 若不在有效时段，自动将起始时间调整为下一个工作日的7:00（WORKDAY.INTL(G2,0,"0000011")返回下一个工作日日期，搭配TIME(7,0,0)得到7点整）

2. 计算中间完整工作日的分钟数 #

(NETWORKDAYS.INTL(修正后起始时间,J2,"0000011")-1)*540

• NETWORKDAYS.INTL(...)：统计修正后起始时间到关闭时间之间的工作日总数（"0000011"代表周六、周日休息）
• -1：排除起始日和结束日，只计算中间完整的工作日
• *540：每个工作日有效时长为9小时（540分钟）

3. 计算起始日的有效分钟数 #

MAX(0,MIN(INT(修正后起始时间)+TIME(16,0,0),J2)-MAX(修正后起始时间,INT(修正后起始时间)+TIME(7,0,0)))

• 取起始日16:00和关闭时间的较小值，减去修正后起始时间和起始日7:00的较大值，结果为负则取0（避免无效时长）

4. 计算结束日的有效分钟数 #

MAX(0,MIN(J2,INT(J2)+TIME(16,0,0))-MAX(修正后起始时间,INT(J2)+TIME(7,0,0)))

• 取关闭时间和结束日16:00的较小值，减去修正后起始时间和结束日7:00的较大值，结果为负则取0

5. 转换为纯分钟数 #

*1440：Excel中1天对应1个数值单位，乘以1440（24×60）将时间差转换为纯分钟数，符合要求的数字格式

注意事项 #

• 确保G列（创建时间）和J列（关闭时间）是Excel可识别的日期时间格式
• 若工作时段调整，只需修改公式中的TIME(7,0,0)和TIME(16,0,0)即可
• MAX(0,...)用于过滤起始时间晚于关闭时间的异常情况，返回0分钟

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