【原】惊喜！难倒80%人的时间问题终于有解了

你好，我是刘卓。欢迎来到我的公号，excel函数解析。今天咱们来分享一个比较复杂的时间问题的解法。计算从一段时间中剔除掉指定时段后剩余的时长。很多小伙伴也会问这类问题。

-01-

具体应用

1.计算剔除掉每天8:00-12:00后剩余的时长

如下图所示，A列是开始时间，B列是结束时间。计算从开始到结束的这段时间中剔除掉每天的8点到12点的时段后，剩余的时长。

比如从2020/4/1 8:30:00到2020/4/2 7:30:00一共是23小时，剔除掉2020/4/1 8:30:00到2020/4/1 12:00:00的3.5小时，剩余19.5小时。

这个问题之所以难，是因为开始时间和结束时间可能是同一天的，也可能是跨天的。这样就导致考虑的情况非常多，可能最后把自己都搞蒙了，还没有把所有的情况列举完。

这时我们就要换一种思路了， 在C3单元格输入下面的公式，按三键结束。

=ROUND((B3-A3)*24-SUM(TEXT(MOD(A3:B3,1)*24-8,"[<]!0;[<4];4")*{-1,1},INT(A3:B3)*{-4,4}),2)

这个公式分为两部分，第1部分的(B3-A3)*24是计算开始时间到结束时间共有多少小时。第2部分的sum函数是计算落在8:00~12:00的小时数。用第1部分减去第2部分就是我们要的结果。

重点就在第2部分，理解了它的思路，这类题就好做了。sum有2个参数，第1参数是个text函数，用来计算开始时间和结束时间占8:00~12:00的时长；第2参数是个int函数，用来计算跨天时整天数占8:00~12:00的时长。

MOD(A3:B3,1)*24这部分先用mod从开始和结束的日期时间中取出时间，再乘以24就转化为小时数。

比如从A3和B3中分别取出时间"8:30"和"7:30"，再乘以24转化为小时数8.5和7.5。

MOD(A3:B3,1)*24-8用上一步的小时数减去8，也就是减去8点，作为text的第1参数，返回的结果为{0.5,-0.5}。

text的第2参数"[<]!0;[<4];4"是格式代码，分为3部分。意思是如果小于0，强制显示为0；如果不小于0但小于4，显示为它本身；如果不小于4，显示为4。

text返回的结果为{"0.5","0"}，这样就得到了开始时间和结束时间占8:00~12:00的时长。比如8:30占了半小时，也就是0.5小时；7:30占了0小时。

然后用text的结果{"0.5","0"}乘以{-1,1}得到{-0.5,0}，目的是为了将开始时间占8:00~12:00的时长变为负数，结束时间占8:00~12:00的时长变为正数。

INT(A3:B3)*{-4,4}这部分算的是整天占8:00~12:00的时长。先用int从开始和结束的日期时间中取出日期，然后乘以{-4,4}。4是8:00~12:00的时长，共4小时。

最后用sum函数对它的2个参数求和，就得到了8:00~12:00的所有时长。

上面的文字更多的是说明函数的计算过程，思路可能没有说明白，下面就用2020/4/1 8:30:00到2020/4/2 7:30:00为例说明一下。

开始时间8:30占8:00~12:00的时间是半小时，结束时间7:30占8:00~12:00的时间是0小时。可以把8:30看作0:00~8:30，7:30看作0:00~7:30。

然后用2020/4/2减去2020/4/1得到1，这个1可以看作除去最后1天的整天数。如果中间间隔了好多天，那么就是第1天和中间多天的整天数。它们占8:00~12:00的时间是4小时，所以用天数乘以4。这里天数是1，那么它就是4小时。

最后用这个4小时减去开始的半小时再加上结束的0小时，就是占8:00~12:00的总时长。大家最好画个数轴，方便理解。

除了用上面的方法，还可以用下面的公式：

=ROUND(SUM(TEXT(MOD(A3:B3,1)*24-{0;8;12},"[<]!0")*{1;-1;1}*{-1,1},INT(A3:B3)*{-1,1}*20),2)

这个公式就更难理解了，感兴趣的可以研究下。

链接：

https://pan.baidu.com/s/1p5CDo8HBwIQFTDCjvf-Tgg

提取码：r959