你好，我是刘卓。欢迎来到我的公号，excel函数解析。今天来分享一个计算实际工时的方法。主要用到前两篇文章的知识，一个是text的分段求和，一个是计算在某段时间内的时长。
1.计算实际工时
下图左表是实际工作的开始和结束时间。右表是正常的上班时间，上午是8:30~12:00，下午是13:00~17:30。求实际工作的时长。
比如，下图第1个的实际工时是5小时。上午从11:00工作到12:00，是1小时；下午从13:00工作到17:00，是4小时。前天文章的问题是计算开始时间到结束时间在一个时段内的时长，今天的问题是计算在多个时段内的时长，无疑增加了难度。其实计算的思路大体上是差不多的，但是还要用到text的分段求和。
在C3单元格输入下面的公式，按三键结束，向下填充。
=ROUND(SUM(TEXT(MOD(A3:B3,1)-TIME({8;12;13;17},{30;0;0;30},),"[<]!0")*{1;-1;1;-1}*{-1,1}*24,INT(A3:B3)*{-1,1}*8),2)
下面以开始时间2020/4/1 11:00和结束时间2020/4/1 17:00为例说明一下思路。首先提取出它们各自的时间"11:00"和"17:00"，然后分别算出"11:00"和"17:00"在上班时段8:30-12:00和13:00-17:30中所占的总时长。比如"11:00"在8:30-12:00中占的时长是2.5小时，在13:00-17:30中占的时长是0小时，总时长是2.5小时。"17:00"在8:30-12:00中占的时长是3.5小时，在13:00-17:30中占的时长是4小时，总时长是7.5小时。接下来还要算出中间跨天的(除去最后1天的所有天)在上班时段所占的总时长。当然现在的例子是同1天的，所以中间跨天的上班总时长为0。最后用中间跨天的上班总时长-开始时间在上班时段所占的总时长+结束时间在上班时段所占的总时长，也就是0-2.5+7.5=5。上面就是总的思路，主要分为3部分：开始时间所占的时长，结束时间所占的时长，中间跨天的所占的时长。你最好画一个坐标图来理解时间的关系。知道了总的思路，下面的问题就是如何求各部分所占的时长。其中中间跨天的时长最好算，因为它占了所有的上班时长，比如1天上班8小时，它就占8小时。而它的天数是用结束日期减去开始日期。它占的时长就是天数*8，也就是INT(A3:B3)*{-1,1}*8这部分。开始时间和结束时间在上班的两个时段中所占的总时长就不好算了，要用到text的分段求和。也就是TEXT(MOD(A3:B3,1)-TIME({8;12;13;17},{30;0;0;30},),"[<]!0")*{1;-1;1;-1}这部分。
其中MOD(A3:B3,1)取出开始和结束的时间，TIME({8;12;13;17},{30;0;0;30},)构造出时间段{"8:30";"12:00";"13:00";"17:30"}，如下图所示。{1;-1;1;-1}是用下一个减上一个，符合text分段求和的原理。
我们知道"17:00"在上班的两个时段中所占的总时长是7.5小时，用公式=SUM(TEXT("17:00"-TIME({8;12;13;17},{30;0;0;30},),"[<]!0")*{1;-1;1;-1})*24算出来的结果也是7.5小时。只要理解了text分段求和的原理，以及时间加减的逻辑，今天的公式就好理解了。今天主要讲逻辑关系，至于公式的运算过程你自己查看。
https://pan.baidu.com/s/1vFEAjCAwOx9SltT5q1d7nA
