excel - 将计算重叠日期数组中总小时数的公式

Question

我已经尝试解决一个问题有一段时间了，但我只是没有得到我需要的结果。我有一个电子表格，其中记录了机械店的工时，我想了解每个技工的工时。该系统使用未结工单来计算工时，但它允许为一名机械师打开多个工单，并且这些工单的日期/时间通常会重叠。

例如，技工 A 在同一天打开三个工单:

WO #1 opened on 5/1/2015 @ 12:00 noon, closed at 4 pm.  
WO #2 opened on 5/1/2015 @ 1pm and closed at 6pm.  
WO #3 opened on 5/1/2015 @ 2pm, closed @ 3pm.

系统将按 WO #1 的 4 小时、WO #2 的 5 小时和 1 小时计算总工时WO #3 总共需要 10 小时的劳动。事实上，由于 WO 时间重叠，机械师只工作了 6 小时。

我需要一个公式，该公式将 1) 指出重叠发生的位置，2) 计算实际工时，而不是系统工时。

我想要一些可以指出重叠发生位置的东西，以及可以计算/返回实际小时数的东西。这在你的脑海中一次性完成很容易，但我正在处理大型数据集，并且手动进行这些计算花费了我大量的时间。我玩过 MIN/MAX 和 SUMPRODUCT，但我似乎无法获得返回我正在寻找的结果的公式。

供引用的示例数据图像:

Answer 1

对于您的示例，假设开始时间/日期为 B2:B6结束时间/日期在 C2:C6您可以使用此公式获取总(非重叠)小时

=SUMPRODUCT((COUNTIFS(B2:B6,"<"&MIN(B2:B6)+ROW(INDIRECT("1:"&ROUND((MAX(C2:C6)-MIN(B2:B6))*1440,0)))/1440-1/2880,C2:C6,">"&MIN(B2:B6)+ROW(INDIRECT("1:"&ROUND((MAX(C2:C6)-MIN(B2:B6))*1440,0)))/1440-1/2880)>0)+0)/60

这将结果显示为十进制小时数。

对于该示例，我假设您想要使用整个范围 - 如果您愿意，可以在公式中添加另一个条件以仅计算特定机制

该公式实际上会测试该时间段内的每一分钟，以查看它是否存在于其中一个时间段中，如果存在则计算......但仅一次 - 请参阅屏幕截图: