Find total duration of many overlapping times

Question

I have a list of dates and times for employee time sheets. The times begin in column F, and end in column G. Sometimes there are overlapping times for projects. The employee does not get paid for overlapping projects, yet we need to track each project separately. I would like to be able to look at columns E, F and G and find any overlapping projects, and return a single time entry. In the example below, notice that line 1 does NOT overlap with the others, but that there is a series of overlapping entries in lines 2-6. They don't necessarily all overlap, but are more like a "chain." I want to write a formula (not a script) to solve this.

+---+------------+------------+----------+
|   |     E      |       F    |    G     |
+---+------------+------------+----------+
| 1 | 10/11/2017 | 12:30 PM   |  1:00 PM |
| 2 | 10/11/2017 |  1:00 PM   |  3:00 PM |
| 3 | 10/11/2017 |  2:15 PM   |  6:45 PM |
| 4 | 10/11/2017 |  2:30 PM   |  3:00 PM |
| 5 | 10/11/2017 |  2:15 PM   |  6:45 PM |
| 6 | 10/11/2017 |  3:00 PM   |  6:45 PM |
+---+------------+------------+----------+

I would want to evaluate these columns and return the total duration of each "chain" on the final line of the series of overlaps. In my example below, we'll put that in column H. It finds 5.75 hours for the series that begins in row 2 and ends in row 6 (1 pm to 6:45 pm).

+---+------------+------------+----------+------------+
|   |     E      |       F    |    G     |      H     |
+---+------------+------------+----------+------------+
| 1 | 10/11/2017 | 12:30 PM   |  1:00 PM |    0.5     |
| 2 | 10/11/2017 |  1:00 PM   |  3:00 PM |  overlap   |
| 3 | 10/11/2017 |  2:15 PM   |  6:45 PM |  overlap   |
| 4 | 10/11/2017 |  2:30 PM   |  3:00 PM |  overlap   |
| 5 | 10/11/2017 |  2:15 PM   |  6:45 PM |  overlap   |
| 6 | 10/11/2017 |  3:00 PM   |  6:45 PM |   5.75     |
+---+------------+------------+----------+------------+

I've tried writing queries, but keep finding myself back at the beginning. If anyone has a suggestion, I'd love to know it! Thank you in advance.

Neill

Answer 1

My Solution

To solve this I need 2 extra columns:

Step 1. Return "overlap" or "ok"

Two lines overlap when one end is inside the other:

I made a query formula to check this:

=if(QUERY(ArrayFormula({value(E1:E+F1:F),VALUE(E1:E+G1:G)}), "select count(Col1) where Col1 < "&value(G1+E1-1/10^4)&" and Col2 > "&value(F1+E1+1/10^4)&" label Count(Col1) ''",0)>1,"overlap","ok")

Drag the formula down. The result is column:

ok
overlap
overlap
overlap
overlap
ok
ok
overlap
overlap
overlap
overlap
ok

In the formula:

• value is used to compare numbers. Must compare each pare: date + time.
• -1/10^4 and +1/10^4 is used because of imprecision in query

Step 2. Get Time Chains

This part is tricky. My solution will only work if data is sorted like in the example.

Enter 1 in cell I1. In cell I2 enter the formula:

=if(or(and(H1=H2,H2="overlap"),and(H2="ok",H1="overlap")),I1,I1+1)

Drag the formula down. The result is column:

1
2
2
2
2
2
3
4
4
4
4
4

Step3. Get Durations

In J4 paste and copy down the formula:

=if(H1="ok", round(QUERY(ArrayFormula({value(E:E+F:F),VALUE(E:E+G:G),I:I}), "select max(Col2) - min(Col1) where Col3 = "&I1 &" label max(Col2) - min(Col1) ''")*24,2),"")

The query gets max durations by groups, found in step2.

• round is used because of imprecision in query