Formula that will calculate the total number of hours in an overlapping array of dates
I've been trying to solve the problem for a while and I just don't get the results I want. I have a spreadsheet that records the hours of work in a machine shop, and I want to know how many hours of work per mechanic. The system uses open work orders to calculate working hours, however, it allows you to open multiple work orders for the same mechanic, and the date / time of these work orders often overlaps.
For example, 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.
The system will calculate total hours of work in 4
hrs for WO # 1, 5
hrs for WO # 2 and 1
hr for WO # 3 in total 10
hours worked. In fact, the mechanic only performed 6
hours of labor, since the WO time intervals overlapped.
I need a formula (s) that will 1) indicate where the overlap occurs, and 2) calculate the actual hours of operation, not the hours of operation of the system.
I need something to indicate where the overlap is happening and something that can read / return the actual clock. It's easy enough to do it in your mind in a one-off fashion, but I'm working with large datasets and these manual calculations take a ton of my time. I've been playing around 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:
source to share
In your example, assuming start / date at B2:B6
and end / date at C2:C6
, you can use this formula to get generic (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
This shows the result as a decimal number of hours.
In this example, I assumed that you want to use the entire range - if you want to add another criterion to the formula to calculate only for a specific mechanic
The formula actually checks every minute over a period of time to see if it exists in one of the time periods if it is counted .... but only once - see screenshot:
source to share
Regarding the second part of your question, "calculate the actual hours of operation, not the hours of operation of the system", and assuming the data is laid out something like this:
The formula may be =MAX(B2:C4)-MIN(B2:C4)
you need more information on how you want to show the "overlap", ie conditional formatting, etc.
source to share