Sum if time is between 2 time frames

[orochikun]

Hello masters !!

Ok I've been struggling with this and it might be very simple

The formula or vba code that im trying to figure out should

Search for Agent Name ( A:A ) if the agent had a start time that is equal or larger than G1 but equal or lower than H1 time, then the formula should sum column D

tl;dr
Sum the seconds the agent was offline at a particular time

Hyper Thank you for your time !

 

[RasGhul]

Hi Orochikun,

Maybe the following could help, copy the formula down as required;

Book1
	A	B	C	D	E	F	G	H
1	Agent	Start	End	Duration			10:00:31 AM	6:56:50 PM
2	On	10:00:31 AM	10:01:03 AM	32
3	On	12:00:04 PM	12:14:26 PM	862		Agent	Total
4	On	3:10:24 PM	3:10:29 PM	5		On	1787
5	On	5:00:05 PM	5:14:05 PM	840		Or	5
6	On	6:03:01 PM	6:03:04 PM	3
7	On	6:56:05 PM	6:56:50 PM	45
8	Or	8:11:29 AM	8:11:53 AM	24
9	Or	8:12:09 AM	8:12:15 AM	6
10	Or	10:26:57 AM	10:27:02 AM	5
Sheet1

Cell Formulas
Range	Formula
D2	=(C2-B2)*86400
G4	=SUMIFS($D$2:$D$10,$B$2:$B$10,">="&$G$1,$C$2:$C$10,"<="&$H$1,$A$2:$A$10,F4)

 

[orochikun]

Hi Orochikun,

Maybe the following could help, copy the formula down as required;

	A	B	C	D	E	F	G	H
Agent	Start	End	Duration
On
On	Agent	Total
On	On
On	Or
On
On
Or
Or
Or

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10:00:31 AM[/TD]
[TD="align: right"]6:56:50 PM[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10:00:31 AM[/TD]
[TD="align: right"]10:01:03 AM[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]12:00:04 PM[/TD]
[TD="align: right"]12:14:26 PM[/TD]
[TD="align: right"]862[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3:10:24 PM[/TD]
[TD="align: right"]3:10:29 PM[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1787[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5:00:05 PM[/TD]
[TD="align: right"]5:14:05 PM[/TD]
[TD="align: right"]840[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]6:03:01 PM[/TD]
[TD="align: right"]6:03:04 PM[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]6:56:05 PM[/TD]
[TD="align: right"]6:56:50 PM[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]8:11:29 AM[/TD]
[TD="align: right"]8:11:53 AM[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]8:12:09 AM[/TD]
[TD="align: right"]8:12:15 AM[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10:26:57 AM[/TD]
[TD="align: right"]10:27:02 AM[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=(C2-B2)*86400[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=SUMIFS($D$2:$D$10,$B$2:$B$10,">="&$G$1,$C$2:$C$10,"<="&$H$1,$A$2:$A$10,F4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you @RasGhul
Wow that was fast !!

I still have one more issue that is fixable if the agent decides to log out at 08:59:00 and log back in at 09:02:00 then the formula will not work

So in the mean time I moved the intervals to a 2 hour range ( that should in theory encompass all agents login/logouts )

That being said, is there a way to :

Search for the agents in $F:$F
Look at the given time G$1
Calculate that the end time is either 15, 30 or 60 minutes after G$1
Then sum the logged out minutes


This way if the agent logged out at 08:59:00 and logged back in at 09:10:00, the calculation will still resolve With that there should be no way to miss the agent login/logouts regardless of the end time ( since we are not providing a static end time

once again super thank you !

 

[orochikun]

*Correcting a statement*

I still have one more issue (that has a workaround below ) if the agent decides to log out at 08:59:00 and log back in at 09:02:00 then the formula will not work since we specify end time and the agent logout fell outside the box we set.

So in the mean time I moved the intervals to a 2 hour range ( that should in theory encompass all agents login/logouts )

That being said, is there a way to :

Search for the agents in $F:$F
Look at the given time G$1
Calculate that the end time is either 15, 30 or 60 minutes after G$1
Then sum the logged out minutes


This way if the agent logged out at 08:59:00 and logged back in at 09:10:00, the calculation will still resolve with that there should be no way to miss the agent login/logouts regardless of the end time ( since we are not providing a static end time

once again super thank you ![/QUOTE]

 

[RasGhul]

Table created for the requested time blocks;

Also maybe test changing "<="&B$14,$A$2:$A$10,$A15 to "<"&B$14,$A$2:$A$10,$A15 and see what results you get so less than or equal to start time but less than end block only.

Book1
	A	B	C	D	E	F	G
1	Agent	Start	End	Duration			8:00:00 AM
2	On	10:00:31 AM	10:01:03 AM	32
3	On	12:00:04 PM	12:14:26 PM	862
4	On	3:10:24 PM	3:10:29 PM	5
5	On	5:00:05 PM	5:14:05 PM	840
6	On	6:03:01 PM	6:03:04 PM	3
7	On	6:56:05 PM	6:56:50 PM	45
8	Or	8:11:29 AM	8:11:53 AM	24
9	Or	8:12:09 AM	8:12:15 AM	6
10	Or	10:26:57 AM	10:27:02 AM	5
11
12
13		0:15:00	0:30:00	1:00:00
14	Agent	8:15:00	8:30:00	9:00:00
15	On	0	0	0
16	Or	30	30	30
Sheet1

Cell Formulas
Range	Formula
D2	=(C2-B2)*86400
B14	=$G$1+B$13
B15	=SUMIFS($D$2:$D$10,$B$2:$B$10,">="&$G$1,$C$2:$C$10,"<="&B$14,$A$2:$A$10,$A15)

 

[orochikun]

Sorry for the loooong response @RasGhul,

The function worked like a charm !!

 

[RasGhul]

Excellent thanks for the feedback