Sum if time is between 2 time frames

orochikun

New Member
Joined
Feb 26, 2019
Messages
4
Hello masters !!

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

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
EgxlyJA.png


Hyper Thank you for your time !
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi Orochikun,

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



Book1
ABCDEFGH
1AgentStartEndDuration10:00:31 AM6:56:50 PM
2On10:00:31 AM10:01:03 AM32
3On12:00:04 PM12:14:26 PM862AgentTotal
4On3:10:24 PM3:10:29 PM5On1787
5On5:00:05 PM5:14:05 PM840Or5
6On6:03:01 PM6:03:04 PM3
7On6:56:05 PM6:56:50 PM45
8Or8:11:29 AM8:11:53 AM24
9Or8:12:09 AM8:12:15 AM6
10Or10:26:57 AM10:27:02 AM5
Sheet1
Cell Formulas
RangeFormula
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)
 
Upvote 0
Hi Orochikun,

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


ABCDEFGH
AgentStartEndDuration
On
OnAgentTotal
OnOn
OnOr
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 !
 
Upvote 0
*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]
 
Upvote 0
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
ABCDEFG
1AgentStartEndDuration8:00:00 AM
2On10:00:31 AM10:01:03 AM32
3On12:00:04 PM12:14:26 PM862
4On3:10:24 PM3:10:29 PM5
5On5:00:05 PM5:14:05 PM840
6On6:03:01 PM6:03:04 PM3
7On6:56:05 PM6:56:50 PM45
8Or8:11:29 AM8:11:53 AM24
9Or8:12:09 AM8:12:15 AM6
10Or10:26:57 AM10:27:02 AM5
11
12
130:15:000:30:001:00:00
14Agent8:15:008:30:009:00:00
15On000
16Or303030
Sheet1
Cell Formulas
RangeFormula
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)
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top