Calculating actual login hrs in excel using macro or formula

[nikhil_hcl]

Hi,

Can any one help me with this problem. I have this data with me whic
is given below. I want to calculate actual login hrs for this agent wh
is doing chats with multiple IDS i.e. Monika, catherine, nicole i.e.
for how much time this agent was not doing chats with any of thre
IDS.


Agent Start time End time Duration

Monika 2:05:11 AM 2:26:34 AM 0:21:23
Catherine 2:23:51 AM 3:30:47 AM 1:06:56
Monika 2:27:54 AM 2:33:05 AM 0:05:11
Monika 3:14:16 AM 3:30:06 AM 0:15:50
Monika 3:41:55 AM 3:47:02 AM 0:05:07
Monika 3:54:10 AM 4:24:03 AM 0:29:53
Catherine 4:12:35 AM 4:27:59 AM 0:15:24
Monika 4:27:29 AM 4:36:56 AM 0:09:27
Catherine 4:34:35 AM 4:39:45 AM 0:05:10
Monika 5:20:04 AM 6:47:48 AM 1:27:44
Catherine 5:22:38 AM 6:09:37 AM 0:46:59
Nicole 5:55:54 AM 6:07:28 AM 0:11:34
Catherine 6:12:26 AM 6:17:43 AM 0:05:17
Catherine 6:18:43 AM 6:54:15 AM 0:35:32
Monika 6:47:48 AM 7:07:10 AM 0:19:22
Catherine 6:54:41 AM 7:04:56 AM 0:10:15
Monika 7:07:15 AM 7:34:20 AM 0:27:05
Catherine 7:11:30 AM 8:17:51 AM 1:06:21
Monika 7:34:26 AM 7:43:32 AM 0:09:06
Monika 7:55:46 AM 8:39:01 AM 0:43:15
Catherine 8:19:36 AM 8:27:49 AM 0:08:13
Catherine 8:28:51 AM 8:45:14 AM 0:16:23
Monika 8:39:09 AM 8:47:37 AM 0:08:28
Monika 8:52:59 AM 9:14:32 AM 0:21:33
Monika 9:19:00 AM 9:45:26 AM 0:26:26
Catherine 9:39:32 AM 9:44:39 AM 0:05:0

 

[Frank Kabel]

Hi
try
=SUMIF(A1:A100,"Monika",D1100)
and format the resulting cell with the custom format
[hh]:mm