Sum time gaps

[bored622]

Hello everyone,

I'm trying to get the time gaps for each user. The problem is that the sum is thrown off once the time stamp goes to the following user. Is there a better formula that ensures that it does not sum the time gap if it is a different user? The data is formatted this way because our software formats the exported file in this format. I highlighted the cells that are affected by the issue.

time gaps.xlsm
	A	B	C	D	E	F	G	H
1	User	Time stamps	Time Gaps				User	Time Gap Sum
2	1	23:23					1	0:38
3	1	23:45	0:22				2	0:55
4	1	0:01	0:16				3	23:14
5	2	23:36	23:35
6	2	23:45	0:09
7	2	0:56	1:11
8	3	23:00	22:04
9	3	23:35	0:35
10	3	0:10	0:35
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=SUMIF(A:A,G2,C:C)
C3:C10	C3	=IF(B3>B2,B3-B2,1-B2+B3)

 

[FormR]

So you're looking for an alternative formula for column C?

If so, perhaps:

Book1
	A	B	C	D	E	F	G	H
1	User	Time stamps	Time Gaps				User	Time Gap Sum
2	1	23:23:00	00:00:00				1	00:38:00
3	1	23:45:00	00:22:00				2	01:20:00
4	1	00:01:00	00:16:00				3	01:10:00
5	2	23:36:00	00:00:00
6	2	23:45:00	00:09:00
7	2	00:56:00	01:11:00
8	3	23:00:00	00:00:00
9	3	23:35:00	00:35:00
10	3	00:10:00	00:35:00
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=SUMIF(A:A,G2,C:C)
C2:C10	C2	=IF(A2=A1,MOD(B2-B1,1),0)

 

[AlanY]

will this works for you?

Book1
	A	B	C	D	E	F	G	H
1	User	Time stamps	Time Gaps				User	Time Gap Sum
2	1	23:23					1	00:38
3	1	23:45	00:22				2	01:20
4	1	00:01	00:16				3	01:10
5	2	23:36
6	2	23:45	00:09
7	2	00:56	01:11
8	3	23:00
9	3	23:35	00:35
10	3	00:10	00:35
Sheet2

Cell Formulas
Range	Formula
H2:H4	H2	=SUMIF(A:A,G2,C:C)
C2:C10	C2	=IF(A1<>A2,"",IF(B2>B1,B2-B1,1-B1+B2))

 

[bored622]

Wow both worked perfectly! Thank you!

 

[AlanY]

Glad to help