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
Bookmarks