如何在完成时长中批量剔除非工作时间段？

rgeist

这里有两列时间数据，A为时间开始，B为时间结束，C目前设置了函数“=TEXT(B2-A2,"")/3600”，可以精确计算出以小时为单位的完成时长
现在遇到一个进一步的问题求助各位大神
预设每日18:00-次日9:00共15个小时为非工作时间，如何在C列数据的基础上剔除在这个时间段内的时长？（C列结果要保留）
实际场景举例：12月1日6:00开始，12月3日12:00结束，如果剔除非工作时间时长，那么应该减去3小时+15小时+15小时共33小时
12月1日6:00开始，12月1日21:00结束，那么应该减去3小时+3小时共6小时
难就难在每行数据的开始和结束存在各种情况，有些是开始时间处于非工作时间内，有些是结束时间处于非工作时间内，还有些跨了几天这统一的函数该怎么写？谢谢大家

rgeist

求助啊求助，在线等

rgeist

求助啊求助，在线等

rgeist

哪怕分几个步骤能计算出来也可以的

sbdk007

没有模拟结果，猜一个
=ROUND(SUM(--TEXT(MOD(A2:B2,1)*24-{18,9},{"[<-9]9;[<];!0","[>9]9;[>];!0"}),9*(INT(B2)-INT(A2)-1)),2)
把林老师公式抄一遍
=ROUND(SUM(TEXT(18-MOD(A2:B2,1)*24,"[>9]9;[>];!0")*{1,-1},{-9,9}*INT(A2:B2)),2)
数组公式，三键

rgeist

sbdk007 发表于 2020-2-22 12:26
没有模拟结果，猜一个
=ROUND(SUM(--TEXT(MOD(A2:B2,1)*24-{16,9},{"[];!0"}),7*(INT(B2)-INT(A2)-1)),2)
...

复制在D列后提示错误呀

rgeist

sbdk007 发表于 2020-2-22 12:26
没有模拟结果，猜一个
=ROUND(SUM(--TEXT(MOD(A2:B2,1)*24-{16,9},{"[];!0"}),7*(INT(B2)-INT(A2)-1)),2)
...

不好意思本人比较小白一点。能不能具体说一下怎么操作数组

rgeist

sbdk007 发表于 2020-2-22 12:26
没有模拟结果，猜一个
=ROUND(SUM(--TEXT(MOD(A2:B2,1)*24-{16,9},{"[];!0"}),7*(INT(B2)-INT(A2)-1)),2)
...

可以计算了！我用筛选计算的笨办法已经做完了。我用您提供的这个验证下数据，先谢谢

rgeist

sbdk007 发表于 2020-2-22 12:26
没有模拟结果，猜一个
=ROUND(SUM(--TEXT(MOD(A2:B2,1)*24-{16,9},{"[];!0"}),7*(INT(B2)-INT(A2)-1)),2)
...

部分数字对的上，部分数字对不上比如
用您的公司算出来是7小时，而实际上这一条在工作时间内的时长刚好9小时整

sbdk007

rgeist 发表于 2020-2-22 14:32
部分数字对的上，部分数字对不上比如
用您的公司算出来是7小时，而实际上这一条在工作时间内的时长刚好9 ...

根据你的描述，一天的有效工作时间就是早9：00到下午4：00，共7个小时，怎么会变成9个小时