+ Reply to Thread
Results 1 to 6 of 6

Help summing time if an event occurs between two sets of time

  1. #1
    Registered User
    Join Date
    07-22-2017
    Location
    El Paso, TX
    MS-Off Ver
    Excel 2016
    Posts
    9

    Help summing time if an event occurs between two sets of time

    Hi everyone, I need help in creating a formula that does the following: I work at a call center and my agents are either on a call, working emails in between calls or doing nothing waiting for a call. I export the agent activity from a database and I also export the agent email activity from another database so I have two sets of data. One database tells me if an agent is on a call or not. The other database tells me if the agent is working an email or not. So ideally, I would like to get the total time per agent that they are on a call working email, on a call not working email, not on a call working email, and not on a call not working email. I've attached two screenshots of the two databases I extract as well as copying the data below.

    As an example, the agent in my scenario below had a total time of 77 minutes not on a call and not working email. I manually added that by comparing times between start time and end time for both databases in which the state is "available"

    Date Start Time End Time VCID Duration Seconds
    7/19/2017 10:31:23 AM 10:36:41 AM 1149 318
    7/19/2017 10:36:41 AM 10:58:19 AM 1149 1298
    7/19/2017 10:58:19 AM 11:01:46 AM 1149 207
    7/19/2017 11:01:47 AM 11:42:44 AM 1149 2457
    7/19/2017 11:42:45 AM 11:42:57 AM 1149 12
    7/19/2017 11:42:57 AM 11:48:12 AM 1149 315
    7/19/2017 11:48:13 AM 11:48:51 AM 1149 38
    7/19/2017 11:48:51 AM 11:49:15 AM 1149 24
    7/19/2017 11:49:16 AM 11:49:36 AM 1149 20
    7/19/2017 12:15:07 PM 12:16:27 PM 1149 80
    7/19/2017 12:16:27 PM 12:26:48 PM 1149 621
    7/19/2017 12:26:49 PM 12:33:46 PM 1149 417
    7/19/2017 12:33:46 PM 12:34:00 PM 1149 14
    7/19/2017 12:34:00 PM 12:34:10 PM 1149 10
    7/19/2017 12:49:09 PM 12:52:05 PM 1149 176
    7/19/2017 12:52:05 PM 12:52:53 PM 1149 48
    7/19/2017 1:03:19 PM 1:10:04 PM 1149 405
    7/19/2017 2:01:17 PM 2:02:56 PM 1149 99
    7/19/2017 2:02:57 PM 2:04:14 PM 1149 77
    7/19/2017 2:04:14 PM 2:04:28 PM 1149 14
    7/19/2017 2:26:41 PM 3:00:49 PM 1149 2048
    7/19/2017 3:00:49 PM 3:01:05 PM 1149 16
    7/19/2017 3:01:06 PM 3:11:35 PM 1149 629
    7/19/2017 3:11:35 PM 3:32:07 PM 1149 1232
    7/19/2017 3:41:46 PM 4:10:12 PM 1149 1706
    7/19/2017 4:10:12 PM 4:28:27 PM 1149 1095
    7/19/2017 4:28:28 PM 4:30:37 PM 1149 129
    7/19/2017 4:30:37 PM 4:30:50 PM 1149 13
    7/19/2017 4:30:51 PM 4:31:33 PM 1149 42
    7/19/2017 4:31:33 PM 4:37:51 PM 1149 378
    7/19/2017 4:56:05 PM 5:07:21 PM 1149 676
    7/19/2017 5:07:21 PM 5:09:33 PM 1149 132
    7/19/2017 5:09:34 PM 5:09:51 PM 1149 17
    7/19/2017 5:40:15 PM 5:45:56 PM 1149 341
    7/19/2017 5:45:56 PM 6:17:12 PM 1149 1876


    Date Start Time End Time VCID State State Description Duration Seconds
    7/19/2017 10:30:41 AM 10:31:26 AM 1149 1 Avaliable 45
    7/19/2017 10:31:43 AM 10:38:13 AM 1149 1 Avaliable 390
    7/19/2017 10:42:00 AM 10:48:20 AM 1149 1 Avaliable 380
    7/19/2017 10:50:19 AM 10:54:46 AM 1149 1 Avaliable 267
    7/19/2017 10:57:39 AM 11:01:32 AM 1149 1 Avaliable 233
    7/19/2017 11:02:35 AM 11:04:31 AM 1149 1 Avaliable 116
    7/19/2017 11:09:11 AM 11:13:41 AM 1149 1 Avaliable 270
    7/19/2017 11:18:41 AM 11:25:58 AM 1149 1 Avaliable 437
    7/19/2017 11:29:15 AM 11:34:15 AM 1149 1 Avaliable 300
    7/19/2017 11:37:43 AM 11:44:59 AM 1149 1 Avaliable 436
    7/19/2017 11:47:28 AM 11:53:14 AM 1149 1 Avaliable 346
    7/19/2017 11:58:53 AM 12:00:43 PM 1149 1 Avaliable 110
    7/19/2017 12:15:45 PM 12:17:45 PM 1149 1 Avaliable 120
    7/19/2017 12:21:07 PM 12:22:24 PM 1149 1 Avaliable 77
    7/19/2017 12:26:32 PM 12:31:40 PM 1149 1 Avaliable 308
    7/19/2017 12:32:15 PM 12:38:41 PM 1149 1 Avaliable 386
    7/19/2017 1:00:33 PM 1:00:34 PM 1149 1 Avaliable 1
    7/19/2017 1:03:27 PM 1:07:25 PM 1149 1 Avaliable 238
    7/19/2017 1:08:42 PM 1:14:25 PM 1149 1 Avaliable 343
    7/19/2017 1:25:11 PM 1:30:35 PM 1149 1 Avaliable 324
    7/19/2017 2:01:18 PM 2:08:25 PM 1149 1 Avaliable 427
    7/19/2017 2:12:25 PM 2:19:24 PM 1149 1 Avaliable 419
    7/19/2017 2:22:58 PM 2:28:47 PM 1149 1 Avaliable 349
    7/19/2017 2:31:34 PM 2:34:10 PM 1149 1 Avaliable 156
    7/19/2017 2:37:28 PM 2:43:05 PM 1149 1 Avaliable 337
    7/19/2017 2:56:37 PM 3:02:37 PM 1149 1 Avaliable 360
    7/19/2017 3:06:59 PM 3:07:14 PM 1149 1 Avaliable 15
    7/19/2017 3:11:34 PM 3:13:45 PM 1149 1 Avaliable 131
    7/19/2017 3:15:16 PM 3:15:58 PM 1149 1 Avaliable 42
    7/19/2017 3:19:25 PM 3:24:29 PM 1149 1 Avaliable 304
    7/19/2017 3:27:23 PM 3:29:26 PM 1149 1 Avaliable 123
    7/19/2017 3:32:14 PM 3:32:55 PM 1149 1 Avaliable 41
    7/19/2017 3:35:14 PM 3:36:32 PM 1149 1 Avaliable 78
    7/19/2017 3:39:18 PM 3:39:33 PM 1149 1 Avaliable 15
    7/19/2017 3:41:29 PM 3:43:37 PM 1149 1 Avaliable 128
    7/19/2017 3:46:39 PM 3:52:11 PM 1149 1 Avaliable 332
    7/19/2017 3:55:59 PM 4:01:13 PM 1149 1 Avaliable 314
    7/19/2017 4:07:47 PM 4:11:43 PM 1149 1 Avaliable 236
    7/19/2017 4:14:37 PM 4:20:33 PM 1149 1 Avaliable 356
    7/19/2017 4:24:13 PM 4:34:13 PM 1149 1 Avaliable 600
    7/19/2017 4:36:50 PM 4:42:47 PM 1149 1 Avaliable 357
    7/19/2017 4:44:41 PM 4:50:43 PM 1149 1 Avaliable 362
    7/19/2017 4:51:31 PM 4:53:02 PM 1149 1 Avaliable 91
    7/19/2017 4:55:55 PM 5:01:19 PM 1149 1 Avaliable 324
    7/19/2017 5:06:31 PM 5:10:36 PM 1149 1 Avaliable 245
    7/19/2017 5:18:21 PM 5:23:46 PM 1149 1 Avaliable 325
    7/19/2017 5:39:22 PM 5:40:56 PM 1149 1 Avaliable 94
    7/19/2017 5:44:34 PM 5:47:13 PM 1149 1 Avaliable 159
    7/19/2017 5:49:37 PM 6:01:15 PM 1149 1 Avaliable 698
    7/19/2017 6:02:47 PM 6:05:47 PM 1149 1 Avaliable 180
    7/19/2017 6:06:36 PM 6:07:22 PM 1149 1 Avaliable 46
    7/19/2017 6:15:19 PM 6:17:52 PM 1149 1 Avaliable 153
    7/19/2017 6:20:57 PM 6:24:52 PM 1149 1 Avaliable 235
    7/19/2017 6:26:22 PM 6:29:24 PM 1149 1 Avaliable 182
    7/19/2017 6:33:59 PM 6:34:53 PM 1149 1 Avaliable 54
    7/19/2017 6:37:06 PM 6:38:59 PM 1149 1 Avaliable 113
    7/19/2017 6:40:54 PM 6:40:55 PM 1149 1 Avaliable 1
    7/19/2017 6:43:54 PM 6:48:52 PM 1149 1 Avaliable 298
    7/19/2017 6:50:22 PM 6:52:56 PM 1149 1 Avaliable 154
    7/19/2017 6:58:16 PM 7:00:09 PM 1149 1 Avaliable 113

    Example 1.PNGExample 2.PNG

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Help summing time if an event occurs between two sets of time

    Welcome to the forum!

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,956

    Re: Help summing time if an event occurs between two sets of time

    See attachment.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Help summing time if an event occurs between two sets of time

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!

  5. #5
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,956

    Re: Help summing time if an event occurs between two sets of time

    Noted with thanks, AliGW.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Help summing time if an event occurs between two sets of time

    Could you please update the post with the attachment to explain the solution offered? Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Check if event occurs within time range
    By excellinginlife in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 09-20-2016, 10:43 AM
  2. Replies: 2
    Last Post: 09-14-2015, 11:00 AM
  3. Replies: 4
    Last Post: 11-06-2014, 02:34 AM
  4. FREQUENCY - Trying to check how many time an item occurs
    By yousuf0203 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-05-2014, 10:05 AM
  5. Replies: 4
    Last Post: 11-27-2012, 04:42 PM
  6. How to find the time that a max value occurs?
    By tom.bateman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-27-2012, 10:19 PM
  7. Replies: 2
    Last Post: 09-09-2008, 09:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1