4

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.

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.

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.

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.

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: Sample Data

Dan_Solo
  • 53
  • 1
  • 9
  • What's the smallest time unit you use, 15 mins, 5 mins, 1 min? – barry houdini Jun 01 '15 at 17:53
  • We can go down to the minute on time units (i.e. labor hours could feasibly be "9:23.") – Dan_Solo Jun 01 '15 at 17:55
  • If you do not worry about non contiguous ranges then maybe a pivot table could give expected results? Did you try this appoach? If not - can you upload a sample worksheet showing the data layout? – BrakNicku Jun 01 '15 at 17:56
  • I can suggest a complex formula approach but that might not be suitable depending on your data - how much data do you want to apply this to (how many rows) and over what period of time? It would probably be easier with start time and date in the same cell (and end time and date in the same cell) for each row of data, is that possible, or is the setup different? – barry houdini Jun 01 '15 at 18:36
  • It's about 2-300 rows of data and the time span is about a month at a time. The system puts the date and time in the same cell. – Dan_Solo Jun 01 '15 at 21:07

2 Answers2

3

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:

enter image description here

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • This looks very promising Barry. When I change the cell range to account for the full sheet, the formula breaks. Any thoughts? – Dan_Solo Jun 01 '15 at 22:18
  • What did you change it to? Best not to use whole columns but if you use a larger range make sure all the references use the same row numbers. The formula should still work even with blank rows in the range – barry houdini Jun 01 '15 at 22:41
  • I think it works fine, I think I accidentally hit a key stroke that broke it. – Dan_Solo Jun 01 '15 at 22:48
  • That is very clever indeed, building the buckets with `ROW` and then counting. I'm will have to study this one for the future. – Byron Wall Jun 03 '15 at 22:25
  • i don't believe this works if the start time and end time are on separate days – FirefighterBlu3 Jul 03 '16 at 09:40
  • Very late to this but I just want to let you know this is what I had been struggling with for days, and with some minor adjustments this formula is a lifesaver! – Zeretil Nov 21 '17 at 12:53
-1

As to the second portion of your question, "calculate the actual labor hours, not the system labor hours", and assuming that the data is laid out something like this:

enter image description here

The formula could be =MAX(B2:C4)-MIN(B2:C4) Need more information on how you want to show the "overlap", i.e. conditional formatting etc.

Clif
  • 379
  • 3
  • 5
  • 1
    I don't think it will work for non overlapping ranges. – BrakNicku Jun 01 '15 at 17:00
  • 1
    @user3964075, I just ran a test with non overlapping ranges and it worked fine. Now I can see where there might be a problem with non contiguous ranges if that is what you mean. – Clif Jun 01 '15 at 17:08
  • The ones that do not overlap aren't really an issue, the system hours will equal the actual hours in those instances. I basically need something that will aggregate labor hours per mechanic while accounting for overlapping times. Clif, your data layout is more or less what I'm working with, however, assume there is a WO #4 has 5 labor hours on a different day and has no overlap with the first three. I want the formula to tell me that Mechanic A worked a total of 11 hours combined on all 4 WOs. – Dan_Solo Jun 01 '15 at 17:28
  • @pnuts Not sure if I follow your question, what I mean is that some mechanics only have one work order, others have multiple WO's that do not over lap. For those, system labor hours would be the same as actual labor hours. – Dan_Solo Jun 01 '15 at 21:05
  • @pnuts it's a manual process at this point. I sort by the mechanic name and just use my brain to check for overlap. I trust system time where there is no overlap, for the rest it's just tedious number crunching. I can find the MIN/MAX, but the trick is how to program it to know to recognize overlap. – Dan_Solo Jun 01 '15 at 21:20
  • Wondering if someone could kindly point me in the direction of rules for posting answers on this site, so that I can know which one(s) I violated in earning a down vote. I gave an answer based on the question that was asked prior to the edit that added the "Sample data image for reference:" I recognize that the answer selected answers the question "post edit" and I agree that it is superior. However looking at other threads it does not seem like having one of the "un-selected" answers mandates a down vote/ loss of rep, so I assume that I violated some SO rule. – Clif Jun 02 '15 at 00:37