Sumif between two times

DDT123

New Member
Joined
Aug 9, 2011
Messages
36
Office Version
  1. 365
Platform
  1. Windows
Greetings all! I have the below spreadsheet that I'm needing to sum total "paused*" times between two intervals. Some of the paused times bleed over into the following intervals. For example, paused time at 8:18 AM was partially during the 8:15 AM interval and carried thru to the 9:30 AM interval. So total paused time for those intervals should be 15 minutes each. Any help is greatly appreciated!!


Book1
ABCDEFGH
1DescriptionTransaction StartTransaction EndDurationInterval StartSum
2active10/27/2021 7:5610/27/2021 8:180:22:318:00 AM
3paused - other10/27/2021 8:1810/27/2021 9:381:19:418:15 AM
4active10/27/2021 9:3810/27/2021 9:390:01:058:30 AM
5paused - Restroom10/27/2021 9:3910/27/2021 9:390:00:158:45 AM
6paused - other10/27/2021 9:3910/27/2021 9:450:05:259:00 AM
7paused - Break10/27/2021 9:4510/27/2021 10:000:15:119:15 AM
8active10/27/2021 10:0010/27/2021 10:010:00:289:30 AM
9ringing10/27/2021 10:0110/27/2021 10:010:00:039:45 AM
10on call10/27/2021 10:0110/27/2021 10:050:04:5010:00 AM
11wrap up10/27/2021 10:0510/27/2021 10:050:00:0610:15 AM
12paused - Break10/27/2021 10:0510/27/2021 10:060:00:0910:30 AM
13paused - other10/27/2021 10:0610/27/2021 11:191:13:3710:45 AM
14active10/27/2021 11:1910/27/2021 11:240:04:5411:00 AM
15ringing10/27/2021 11:2410/27/2021 11:240:00:0411:15 AM
16on call10/27/2021 11:2410/27/2021 11:310:06:1711:30 AM
17wrap up10/27/2021 11:3110/27/2021 11:310:00:1911:45 AM
18paused - other10/27/2021 11:3110/27/2021 12:321:00:5812:00 PM
19paused - Lunch10/27/2021 12:3210/27/2021 13:030:30:5612:15 PM
20active10/27/2021 13:0310/27/2021 13:030:00:3012:30 PM
21paused - Lunch10/27/2021 13:0310/27/2021 13:030:00:1212:45 PM
22paused - Processing10/27/2021 13:0310/27/2021 15:042:00:511:00 PM
23active10/27/2021 15:0410/27/2021 15:050:00:171:15 PM
24ringing10/27/2021 15:0510/27/2021 15:050:00:041:30 PM
25on call10/27/2021 15:0510/27/2021 15:470:42:391:45 PM
26wrap up10/27/2021 15:4710/27/2021 15:480:00:142:00 PM
27paused - Break10/27/2021 15:4810/27/2021 16:030:15:122:15 PM
28active10/27/2021 16:0310/27/2021 16:040:01:282:30 PM
29paused - Processing10/27/2021 16:0410/27/2021 16:350:30:472:45 PM
303:00 PM
313:15 PM
323:30 PM
333:45 PM
Sheet1
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Will the data to be summarised always be for a single day as per your example?

Can a single pause start before midnight and finish after midnight?
 
Upvote 0
Will the data to be summarised always be for a single day as per your example?

Can a single pause start before midnight and finish after midnight?

Yes, the report will be ran daily, and we'll be looking at the total paused time per interval. The pauses (intervals) will begin at 8 AM and the final interval will be 8 PM.
The example above is for 1 agent, so with all the agents in the full raw report we will need the sum of all agents during interval x. So the 9:00 AM to 9:15 interval may sum to be over 20 hours if multiple agents goes into "pause - break" at the same time.
 
Upvote 0
This has taken multiple failed attempts but I think that I finally have it right. Try this formula in H2 (based on your example) and fill down.
Excel Formula:
=LET(i,--"00:15",ts,MOD($B$2:$B$29,1),te,MOD($C$2:$C$29,1),is,G2,ie,G2+i,p,ISNUMBER(SEARCH("paused",$A$2:$A$29)),
SUM(IF(p,IF(ts<=is,IF(te>=ie,i,IF(te>is,te-is)),IF(ts>is,IF(te<ie,te-ts,IF(ts<ie,ie-ts)))))))
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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