Calculate time difference by group?

cpaching

New Member
Joined
Sep 19, 2018
Messages
6
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?

VisitCountLastAccessedLast Access DateLast Access TimeGap (hms)Tag (+) if gap less than 15 min
25/29/18 8:26 AM05/29/188:26:58 AM
15/29/18 12:07 PM05/29/1812:07:12 PM3:40:14
15/29/18 12:10 PM05/29/1812:10:35 PM0:03:23+
15/29/18 12:14 PM05/29/1812:14:46 PM0:04:11+
15/29/18 12:15 PM05/29/1812:15:11 PM0:00:25+
15/29/18 12:20 PM05/29/1812:20:28 PM0:05:17+
15/29/18 12:22 PM05/29/1812:22:51 PM0:02:23+
15/29/18 12:23 PM05/29/1812:23:10 PM0:00:19+
15/29/18 12:25 PM05/29/1812:25:33 PM0:02:23+
15/29/18 12:26 PM05/29/1812:26:13 PM0:00:40+
15/29/18 12:31 PM05/29/1812:31:48 PM0:05:35+
15/29/18 4:02 PM05/29/184:02:35 PM3:30:47
15/29/18 4:02 PM05/29/184:02:35 PM0:00:00+
15/29/18 4:02 PM05/29/184:02:35 PM0:00:00+

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

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
Welcome to the forum.

Try:


ABCDEFGH
1VisitCountLastAccessedLast Access DateLast Access TimeGap (hms)Tag (+) if gap less than 15 minGrouped total
225/29/2018 8:265/29/20188:26:58 AM
315/29/2018 12:075/29/201812:07:12 PM3:40:14
415/29/2018 12:105/29/201812:10:35 PM0:03:23+
515/29/2018 12:145/29/201812:14:46 PM0:04:11+
615/29/2018 12:155/29/201812:15:11 PM0:00:25+
715/29/2018 12:205/29/201812:20:28 PM0:05:17+
815/29/2018 12:225/29/201812:22:51 PM0:02:23+
915/29/2018 12:235/29/201812:23:10 PM0:00:19+
1015/29/2018 12:255/29/201812:25:33 PM0:02:23+
1115/29/2018 12:265/29/201812:26:13 PM0:00:40+
1215/29/2018 12:315/29/201812:31:48 PM0:05:35+0:24:36
1315/29/2018 16:025/29/20184:02:35 PM3:30:47
1415/29/2018 16:025/29/20184:02:35 PM0:00:00+
1515/29/2018 16:025/29/20184:02:35 PM0:00:00+

<tbody>
</tbody>
Sheet9

Worksheet Formulas
CellFormula
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.
 
Last edited:

cpaching

New Member
Joined
Sep 19, 2018
Messages
6
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
Happy to help! Let us know if you need help deciphering the formula.

:cool:
 

cpaching

New Member
Joined
Sep 19, 2018
Messages
6

ADVERTISEMENT

That would really help if you don't mind.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
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

New Member
Joined
Sep 19, 2018
Messages
6

ADVERTISEMENT

Thank you again, that is very helpful. :)
 

cpaching

New Member
Joined
Sep 19, 2018
Messages
6
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
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))))
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,296
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top