Summing overlapping times (minutes)

[mikes_r]

I'll try to explain the problem as best I can, let me know if anything is unclear.

I do the payroll for a small driving service which pays its drivers for the time they have a client in the car, but I have a problem with the way I currently do it. Currently, the drivers fill out the times they pick up and drop off each client, and I sum the minutes of each drive then use sumifs for the drives within a certain day for each driver to do the payroll. However, sometimes we have 2 different paying clients in the same car at the same time (sometimes picked up or dropped off at different times). During the times with more than one paying client in the car, the driver is paid time and a half, but the way I have it ends up paying them double time instead. The column headings all this is based on is Date, Client Name, Start Time, End Time, Minutes, Driver.

Does anyone have any thoughts or advice on how I could sum the number of minutes of overlap for each driver, preferably without having to use a list of every minute of every day to count overlapping drives? I've spent a couple weeks thinking about this with little progress, so any thoughts would be greatly appreciated.

Note: We currently bill and pay drivers based on 15 minute increments, i.e. a drive from 3:35 pm to 4:13 pm is billed as 45 minutes.

 

[BrianJN1]

Take a look at this thread, particularly the entry by Marcelo Brando.

This looks to be quite close to what you want. I don't fully understand what he has there but if you are in trouble with it, there will be someone ready to guide you.

 

[BrianJN1]

Somewhere I found this a few days back:
=if(or(and(x>=a,x<=b),and(a>=x,a<=y)),"Overlap","Do not overlap")
and applied it to a project needing "overlap" but not quite as you need.
I developed a formula which gives you the minutes of overlap time.
The formula actually offers a Serial_Number for the difference and so that has to be converted by using MINUTE.
The formula is applied to F2 and then dragged down. Unfortunately the formula creates an error #VALUE in F2 because it cannot equate Driver with Sam E1/E2.
Similarly F4 has the same error because Sam and Dave are not equated.
I have overcome that error by using Conditional Formatting down Col F - New Rule/format only cells that contain/select Cell value dropmenu, select "errors", Format, and then choose Font color as white.

I'm sure there is a tidier way to build that formula, but, if my table structure is according to your design then my strategy should work.
Please advise.
NOTE: I just realised that you had a Client column, that means that your formula will have to shifted 1 column to the right, meaning that G2 will contain this formula:

=MINUTE(IF(F2=F1,IF(AND(C1<=C2,D1<=D2),D1-C2,IF(AND(C1>=C2,D1>=D2),D2-C1,IF(AND(C1<=C2,D1>=D2),D2-C2,IF(AND(C1>=C2,D1<=D2),D1-C1,"")))),""))

instead of the one supplied with the graphic; apologies.


Excel 2013/2016

	A	B	C	D	E	F
Date	Start	End	Min	Driver
sam
sam
dave

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/01/2018[/TD]
[TD="align: right"]10:30[/TD]
[TD="align: right"]10:45[/TD]
[TD="align: right"]0:15[/TD]

[TD="align: right"] #VALUE ![/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/01/2018[/TD]
[TD="align: right"]10:30[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]0:30[/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/01/2018[/TD]
[TD="align: right"]10:45[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]0:15[/TD]

[TD="align: right"] #VALUE ![/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=C2-B2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=MINUTE(IF(E2=E1,IF(AND(B1<=B2,C1<=C2),C1-B2,IF(AND(B1>=B2,C1>=C2),C2-B1,IF(AND(B1<=B2,C1>=C2),C2-B2,IF(AND(B1>=B2,C1<=C2),C1-B1,"")))),""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikes_r]

Hey BrianJN1,

This works, thanks so much! I've made a couple changes to it. I adjusted the logical argument of the first IF statement to ensure the dates are the same so that its now IF(AND(F2=F1,A2=A1)...). I've also added an IFERROR to the outside of the code instead of using the conditional formatting (just a personal preference).

=IFERROR(MINUTE(IF(AND(F2=F1,A2=A1),IF(AND(C1<=C2,D1<=D2),D1-C2,IF(AND(C1>=C2,D1>=D2),D2-C1,IF(AND(C1<=C2,D1>=D2),D2-C2,IF(AND(C1>=C2,D1<=D2),D1-C1,"")))),"")),"")

The one note I have for anyone else who may use this formula is that because it only checks for overlap with 1 row above it, the list has to be sorted correctly for it work. It works for me if the list is sorted first by end time, then start time, both smallest to largest, then by date, oldest to newest, and finally by driver.

 

[BrianJN1]

Great. I did expect that modifications needed to be made, and yes, I did expect that if you did not record a driver's progress chronologically then the data would have to be sorted.

I'll take a look at your formula modifications more closely (for my own curiosity) a little later in the day.

 

[barry houdini]

MINUTE function might cause you problems here because it only returns a value between 0 and 59.

If you have results >= 1 hour then that won't work for you. Either remove MINUTE function and just get the results as time values like 0:15 or multiply by 1440 (the number of minutes in a day) to convert a time value to a number of minutes

 

[BrianJN1]

When I wrote the original formula I was aware of the 0-59 limitation of MINUTE but was unaware that 1440 was the magic number to convert the serial number which I was receiving. While I've learnt something of value, I trust mikes_r will take that on board in his formula.

Thanks.