Count number of times event happens.

[Tspears1]

Not sure if this is possible, but I thought would ask. In the below example I need to find out how many times a Tech left a call and then in less than 30 minutes later, that line called again. For example on 10/22/2024 Tech B left line 1 at 1012(military time) and line 1 called again at 1030. This will add 1 to Tech B total. So in this example Tech A = 1, Tech B = 4, Tech C = 2.

Any help with this would be greatly appreciated. I have a very large spreadsheet that I would like to extract this information from.

LINE	DATE	CALL TIME	OUT TIME	TECH
1	10/22/2024	823	845	A
1	10/22/2024	955	1012	B
2	10/22/2024	1030	1035	C
3	10/22/2024	830	840	C
1	10/22/2024	1030	1055	A
2	10/22/2024	1045	1115	A
2	10/23/2024	1425	1450	B
3	10/23/2024	1255	1310	B
2	10/23/2024	1510	1530	C
3	10/23/2024	1330	1345	A
2	10/23/2024	1545	1650	A
1	10/24/2024	750	830	C
1	10/24/2024	910	930	B
3	10/24/2024	820	835	A
3	10/24/2024	850	920	A
1	10/24/2024	955	1020	B

 

[SanjayGMusafir]

Is this what you are looking for? Check this and revert -

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	LINE	DATE	CALL TIME	OUT TIME	TECH	Call Gap	Calls	Repeat Call
2	1	10/22/2024	823	845	A		1	0		A	1
3	1	10/22/2024	955	1012	B	110	2	1		B	3
4	2	10/22/2024	1030	1035	C		1	1		C	2
5	3	10/22/2024	830	840	C		1	0
6	1	10/22/2024	1030	1055	A	18	3	0
7	2	10/22/2024	1045	1115	A	10	2	0
8	2	10/23/2024	1425	1450	B		1	0
9	3	10/23/2024	1255	1310	B		1	1
10	2	10/23/2024	1510	1530	C	60	2	1
11	3	10/23/2024	1330	1345	A	20	2	0
12	2	10/23/2024	1545	1650	A	15	3	0
13	1	10/24/2024	750	830	C		1	0
14	1	10/24/2024	910	930	B	80	2	1
15	3	10/24/2024	820	835	A		1	1
16	3	10/24/2024	850	920	A	15	2	0
17	1	10/24/2024	955	1020	B	25	3	0
Sheet1

Cell Formulas
Range	Formula
F2:F17	F2	=IF(COUNTIFS($A$1:A2,A2,$B$1:B2,B2)>=2,C2-IFERROR(LOOKUP(2,1/($A$1:A1=A2)*($B$1:B1=B2),$D$1:D1),""),"")
G2:G17	G2	=COUNTIFS($A$1:A2,A2,$B$1:B2,B2)
H2:H17	H2	=COUNTIFS(A:A,A2,B:B,B2,G:G,G2+1,F:F,"<"&30)
K2:K4	K2	=SUMIFS($H:$H,$E:$E,J2)

 

[Tspears1]

Thanks Sanjay for the reply, this looks like it works but it did miss an occurrence for Tech B. In line 8, Tech B has an out time of 1450 and then line 2 calls again on line 10 at 1510, this is just 20 minutes and not 60.

 

[SanjayGMusafir]

Thanks Sanjay for the reply, this looks like it works but it did miss an occurrence for Tech B. In line 8, Tech B has an out time of 1450 and then line 2 calls again on line 10 at 1510, this is just 20 minutes and not 60.

I was trying to find where the error is but couldn't locate it... Thanks for pointing that out.

If you may notice, this error is on account of format in which time has been stored. The obvious subtraction of 1450 from 1510 is 60. But when it will be stored in the time format the difference would be 0.5 which will come to 30 minutes. To rectify that error you need to convert recorded military time to the time format that excel understands.

Hope that helps you...

 

[Tspears1]

Thanks for your help. I think I have it now. You guys are the best!

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.

 

[Tspears1]

Glad to help you and thanks for the feedback.

Sanjay, I thought everything was working but it is not. I can't get your repeat calls formula to work. I changed the times back into a time format and also modified your call gap formula to handle a negative number, but for some reason the repeat call formula is not working. I can not install the XL2BB because I do not have admin rights on my computer. I inserted a pic and the formulas used.

CALL TIME - =TIME(INT(C2/100), MOD(C2, 100), 0)
OUT TIME - =TIME(INT(C2/100), MOD(C2, 100), 0)
CALL GAP - =IF(COUNTIFS($A$1:A2,A2,$B$1:B2,B2)>=2,ABS(F2-IFERROR(LOOKUP(2,1/($A$1:A1=A2)*($B$1:B1=B2),$G$1:G1),"")),"")
CALLS - =COUNTIFS($A$1:A2,A2,$B$1:B2,B2)
REPEAT CALLS - =COUNTIFS(A:A,A2,B:B,B2,I:I,I2+1,H:H,"<"&30)

Thank you for your help.

 

[SanjayGMusafir]

but for some reason the repeat call formula is not working

Repeats Call is not working because after changing back to Excel time format you didn't modify Formula.

Use following and revert -

for REPEAT CALLS

=COUNTIFS(A:A,A2,B:B,B2,I:I,I2+1,H:H,"<"&0.5)

 

[Tspears1]

It is still not working, same results as before.

 

[SanjayGMusafir]

It is still not working

My mistake. At times you forget that you are working with time in Excel and do blunders...

Check this and revert -

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	LINE	DATE	CALL TIME	OUT TIME	TECH	CALL TIME	OUT TIME	Call Gap	Calls	Repeat Call
2	1	10/22/2024	823	845	A	8:23 AM	8:45 AM		1	0		A	1
3	1	10/22/2024	955	1012	B	9:55 AM	10:12 AM	0.05	2	1		B	4
4	2	10/22/2024	1030	1035	C	10:30 AM	10:35 AM		1	1		C	2
5	3	10/22/2024	830	840	C	8:30 AM	8:40 AM		1	0
6	1	10/22/2024	1030	1055	A	10:30 AM	10:55 AM	0.01	3	0
7	2	10/22/2024	1045	1115	A	10:45 AM	11:15 AM	0.01	2	0
8	2	10/23/2024	1425	1450	B	2:25 PM	2:50 PM		1	1
9	3	10/23/2024	1255	1310	B	12:55 PM	1:10 PM		1	1		1D	1.00
10	2	10/23/2024	1510	1530	C	3:10 PM	3:30 PM	0.01	2	1		1H	0.04
11	3	10/23/2024	1330	1345	A	1:30 PM	1:45 PM	0.01	2	0		30M	0.02083
12	2	10/23/2024	1545	1650	A	3:45 PM	4:50 PM	0.01	3	0
13	1	10/24/2024	750	830	C	7:50 AM	8:30 AM		1	0
14	1	10/24/2024	910	930	B	9:10 AM	9:30 AM	0.03	2	1
15	3	10/24/2024	820	835	A	8:20 AM	8:35 AM		1	1
16	3	10/24/2024	850	920	A	8:50 AM	9:20 AM	0.01	2	0
17	1	10/24/2024	955	1020	B	9:55 AM	10:20 AM	0.02	3	0
Sheet1

Cell Formulas
Range	Formula
F2:G17	F2	=TIME(INT(C2/100), MOD(C2, 100), 0)
H2:H17	H2	=IF(COUNTIFS($A$1:A2,A2,$B$1:B2,B2)>=2,F2-IFERROR(LOOKUP(2,1/($A$1:A1=A2)*($B$1:B1=B2),$G$1:G1),""),"")
I2:I17	I2	=COUNTIFS($A$1:A2,A2,$B$1:B2,B2)
J2:J17	J2	=COUNTIFS(A:A,A2,B:B,B2,I:I,I2+1,H:H,"<"&1/48)
M2:M4	M2	=SUMIFS($J:$J,$E:$E,L2)
M10	M10	=1/24
M11	M11	=M9/48