Calculate time difference by group?

[cpaching]

I have an Excel question, in the example below I have a very long list of dates times where I added a column to calculate gaps, and tagged rows where the gap were less than 15 minutes to try to determine a possible consecutive activity. Do you have any idea how to get the groups of tagged rows to calculate a time like latest minus earliest for each tagged group?

VisitCount	LastAccessed	Last Access Date	Last Access Time	Gap (hms)	Tag (+) if gap less than 15 min
2	5/29/18 8:26 AM	05/29/18	8:26:58 AM
1	5/29/18 12:07 PM	05/29/18	12:07:12 PM	3:40:14
1	5/29/18 12:10 PM	05/29/18	12:10:35 PM	0:03:23	+
1	5/29/18 12:14 PM	05/29/18	12:14:46 PM	0:04:11	+
1	5/29/18 12:15 PM	05/29/18	12:15:11 PM	0:00:25	+
1	5/29/18 12:20 PM	05/29/18	12:20:28 PM	0:05:17	+
1	5/29/18 12:22 PM	05/29/18	12:22:51 PM	0:02:23	+
1	5/29/18 12:23 PM	05/29/18	12:23:10 PM	0:00:19	+
1	5/29/18 12:25 PM	05/29/18	12:25:33 PM	0:02:23	+
1	5/29/18 12:26 PM	05/29/18	12:26:13 PM	0:00:40	+
1	5/29/18 12:31 PM	05/29/18	12:31:48 PM	0:05:35	+
1	5/29/18 4:02 PM	05/29/18	4:02:35 PM	3:30:47
1	5/29/18 4:02 PM	05/29/18	4:02:35 PM	0:00:00	+
1	5/29/18 4:02 PM	05/29/18	4:02:35 PM	0:00:00	+

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

Welcome to the forum.

Try:

	A	B	C	D	E	F	G	H
1	VisitCount	LastAccessed	Last Access Date	Last Access Time	Gap (hms)	Tag (+) if gap less than 15 min	Grouped total
2	2	5/29/2018 8:26	5/29/2018	8:26:58 AM
3	1	5/29/2018 12:07	5/29/2018	12:07:12 PM	3:40:14
4	1	5/29/2018 12:10	5/29/2018	12:10:35 PM	0:03:23	+
5	1	5/29/2018 12:14	5/29/2018	12:14:46 PM	0:04:11	+
6	1	5/29/2018 12:15	5/29/2018	12:15:11 PM	0:00:25	+
7	1	5/29/2018 12:20	5/29/2018	12:20:28 PM	0:05:17	+
8	1	5/29/2018 12:22	5/29/2018	12:22:51 PM	0:02:23	+
9	1	5/29/2018 12:23	5/29/2018	12:23:10 PM	0:00:19	+
10	1	5/29/2018 12:25	5/29/2018	12:25:33 PM	0:02:23	+
11	1	5/29/2018 12:26	5/29/2018	12:26:13 PM	0:00:40	+
12	1	5/29/2018 12:31	5/29/2018	12:31:48 PM	0:05:35	+	0:24:36
13	1	5/29/2018 16:02	5/29/2018	4:02:35 PM	3:30:47
14	1	5/29/2018 16:02	5/29/2018	4:02:35 PM	0:00:00	+
15	1	5/29/2018 16:02	5/29/2018	4:02:35 PM	0:00:00	+

<tbody>
</tbody>

Sheet9​

Worksheet Formulas Cell Formula G3 =IF(E3> TIMEVALUE("00:15:00"),"",IF(E4< TIMEVALUE("00:15:00"),"",SUM(INDEX($E$2:$E2,LOOKUP(2,1/($E$2:$E2> TIMEVALUE("00:15:00")),ROW($E$2:$E2)-ROW($E$2)+2)):E3))) <tbody> </tbody>

<tbody>
</tbody>

I ended up not used the F column, I used the TIMEVALUE function to look at the E column directly, but that's easy to change if you want.

 

[cpaching]

Ok Eric, that is magic! I don't understand it yet but it worked Thank you very much for your expertise and the quick response.

 

[Eric W]

Happy to help! Let us know if you need help deciphering the formula.

 

[cpaching]

That would really help if you don't mind.

 

[Eric W]

Sure.

=IF(E3> TIMEVALUE("00:15:00"),"",IF(E4< TIMEVALUE("00:15:00"),"",SUM(INDEX($E$2:$E2,LOOKUP(2,1/($E$2:$E2> TIMEVALUE("00:15:00")),ROW($E$2:$E2)-ROW($E$2)+2)):E3)))

The part in red just says if the value on the current row is over 15 minutes, then just show an empty cell. Otherwise, it must be less than 15 minutes, so we want to include it in a group range. But we only want to put the group sum on the last row of the range, so if the next row is less than 15, it must also be part of the group, so we show an empty cell too (the green part).

So if we get to the last part of the formula, we know we're on the last row of a group. We want to sum up the whole group. In the example, this happens on row 12. What we want to do is sum up E4:E12, like this SUM(E4:E12). The E12 cell is easy, it's the current row (in blue). To find the E4, we have to find the start of the group, which would be the row right after a value > 15. And if you have a long range, it has to be the one just before the current row, not one from farther up in the range.

This construct:

LOOKUP(2,1/($E$2:$E2> TIMEVALUE("00:15:00")),ROW($E$2:$E2)-ROW($E$2)+2)

is used to find the last value > 15 before the current row. Notice how the range is defined as $E$2:$E2. So as you drag the formula down the column the top row stays the same, but the bottom row will always be the row just above the current row. On row 12 it's $E$2:$E11. The part in red checks each cell in that range, and returns an array of TRUE/FALSE values, like this {FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}. Since TRUE = 1 and FALSE = 0, taking the reciprocal (blue) changes that array to {#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}. Now LOOKUP looks for a 2, which it will never find, since the only possible values are 1 and #DIV/0!. So a quirk of LOOKUP is that it will return the position of the last 1 in that array, or 2. Now if you take ROW($E$2:$E11)-ROW($E$2)+1, you get {1,2,3,4,5,6,7,8,9,10}, which is the offsets into the range. Note that I actually add 2 instead of 1, so this gives us the offset after the row with the value > 15, or 3. Finally, we give the 3 to INDEX, or INDEX($E$2:$E11,3), which resolves to E4, which is what we were looking for.

Whew! There are some tricky concepts there that you won't find in a basic formula primer, but they're not too hard once you look at them. Walk through the formula with the Evaluate Formula tool on the Formulas tab, that'll help too.

Good luck!

 

[cpaching]

Thank you again, that is very helpful.

 

[cpaching]

Hi Eric, I hate to bother you again but I have one more question - how can I customize this formula to subtract the last time in the tagged group from the first time in the tagged group to get elapsed time for the group instead of a sum of the the gap minutes?

 

[Eric W]

In G3:

=IF(E3>TIMEVALUE("00:15:00"),"",IF(E4< TIMEVALUE("00:15:00"),"",D3-INDEX($D$2:$D2,LOOKUP(2,1/($E$2:$E2>TIMEVALUE("00:15:00")),ROW($E$2:$E2)-ROW($E$2)+2))))

 

[cpaching]

Thank you again Eric!