计算重叠日期数组中总小时数的公式 [英] Formula that will calculate total hours in overlapping date array

问题描述

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

I've been trying to solve an issue for a while now and I'm just not getting the results I need. I have a spreadsheet that captures labor hours at a mechanic shop and I want to find out how many hours of labor per mechanic. The system uses open work-orders to calculate labor hours, however it allows for multiple work-orders to be open for one mechanic and the date/time of those work-orders often overlaps.

例如，机械师 A 在同一天打开了三个工单:

For instance, Mechanic A opens three work-orders on the same day:

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.

系统将在4 小时为WO #1、5 小时为WO #2 和1 hrs 计算总工时对于 WO #3，总共 10 小时的劳动.实际上，由于 WO 时间重叠，机械师只做了 6 小时的劳动.

The system will calculate the total labor hours at 4 hrs for WO #1, 5 hrs for WO #2, and 1 hr for WO #3 for a total of 10 hours labor. In reality, the mechanic only did 6 hours of labor since the WO times overlap.

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

What I need is a formula(s) that will 1) point out where overlap occurs and 2) calculate the actual labor hours, not the system labor hours.

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

I'd like something that points out where overlap occurs and something that can count/return actual hours. This is easy enough to do in your mind on one-off basis, but I'm working with large data sets and doing these calculations manually is taking a ton of my time. I have played with MIN/MAX and SUMPRODUCT but I can't seem to get the formulas to return the results I'm looking for.

示例数据图像供参考:

推荐答案

例如，假设开始时间/日期在 B2:B6 中，结束时间/日期在 C2:C6 您可以使用此公式获得总(非重叠)小时数

For your example, assuming start times/dates in B2:B6 and end times/dates in C2:C6 you can use this formula to get the total (non-overlapping) hours

=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

结果显示为十进制小时数.

That shows the result as a decimal number of hours.

对于那个例子，我假设你想使用整个范围 - 如果你想你可以在公式中添加另一个标准来只计算一个特定的机制

For that example I assumed you want to use the whole range - if you want you can add another criteria in to the formula to only calculate for a specific mechanic

该公式实际上会在时间段内的每一分钟进行测试，以查看它是否存在于某个时间段内，如果存在，则计算......但仅计算一次 - 见截图:

The formula actually tests every minute within the time period to see whether it exists in one of the time periods, if so it's counted....but only once - see screenshot:

这篇关于计算重叠日期数组中总小时数的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！