将在重叠日期数组中计算总小时数的公式 [英] 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.

系统将计算总工时10小时的总工时，其中WO#1为4小时，WO#2为5小时，WO#3为1小时.实际上，由于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.

样本数据图像供参考:

Sample data image for reference:

推荐答案

在您的示例中，假设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屋！