工作日工时计算公式问题：8-18点工作制下格式显示异常

我们采用工作日8:00-18:00工作制，周末休息。任务可随时进入系统，但仅在工作时段处理。原本使用以下公式提取任务工作时长：

=(NETWORKDAYS(B2,C2)-1)*("18:00:00"-"8:00:00")+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),"18:00:00","8:00:00"),"18:00:00")-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),"18:00:00","8:00:00")

问题描述 #

当实际工作时长为47小时54分47秒（对应需求的4天7小时54分47秒）时，用[h]:mm:ss格式显示正常，但设置为d:hh:mm:ss格式时，得到的结果是1天23小时54分43秒，不符合需求。

样本数据 #

注：「耗时」列为包含周末的总时长，是原始数据。

问题原因 #

原公式计算的是总工作时长（以Excel时间单位表示，1天=24小时），而我们的工作日有效时长为10小时/天。当使用d:hh:mm:ss格式时，Excel会按自然日（24小时）解析时长，导致47小时被错误解析为1天23小时（24+23=47），而非需求的4天7小时（4×10+7=47）。

修正方案 #

方案1：直接生成“X天Y小时Z分W秒”文本结果

该方案通过计算总工作小时数，拆分出工作日数、剩余小时、分、秒，再拼接成符合需求的文本格式：

=INT((NETWORKDAYS(B2,C2)-1)*10 + IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),"18:00:00","8:00:00"),"18:00:00")*24 - MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),"18:00:00","8:00:00")*24)/10 & "天" & 
TEXT(MOD((NETWORKDAYS(B2,C2)-1)*10 + IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),"18:00:00","8:00:00"),"18:00:00")*24 - MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),"18:00:00","8:00:00")*24,10),"0") & "小时" & 
TEXT(MOD((NETWORKDAYS(B2,C2)-1)*10 + IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),"18:00:00","8:00:00"),"18:00:00")*24 - MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),"18:00:00","8:00:00")*24,1)*60,"0") & "分" & 
TEXT(MOD((NETWORKDAYS(B2,C2)-1)*10 + IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),"18:00:00","8:00:00"),"18:00:00")*24 - MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),"18:00:00","8:00:00")*24,1/60)*60,"0") & "秒"

方案2：适配自定义时间格式（d代表10小时工作日）

若需保留数值格式以便后续计算，可将总工作时长转换为以“10小时=1工作日”为单位的数值，再设置自定义格式：

1. 使用公式计算转换后的数值：

=((NETWORKDAYS(B2,C2)-1)*10 + IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),"18:00:00","8:00:00"),"18:00:00")*24 - MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),"18:00:00","8:00:00")*24)/10

2. 设置单元格自定义格式为：d"天"hh"小时"mm"分"ss"秒"
   此时数值的整数部分为工作日数，小数部分会自动转换为剩余的小时、分、秒（基于10小时工作日的比例）。

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