calculate available time

[Afarag]

Dears,

please i need help in an issue related to mathematics somehow, need some ideas to get the desired request,
in recap, i have a worksheet that have some data listed to:
>>"Agent Name",
>>"Chat ID", this column have an Unique IDs for chats,
>>"Visit Time" this time is concern the start time of the chat,
>>"Interval", i rounded the visit time to Quarters,
>>then a column filled with the "End time" for the Chat,
>>at last "Chat Duration" this concern the duration of each chat,
any chat have start time, end time and Duration.
may every agent receive 2 chats or 3 at the same time, so that we can notice the chat duration at interval is "0:28:00", while the interval duration is "0:15:00".
what i looking for is needing to the available time or the free time for each agent in each interval, in considering the cross time chats: it's mean the chat may start in interval and end in another interval, and the agent may have more than one chat in the interval time, therefore i can't get the free time by ("0:15:00"-chat time)

hope if that is available to be handled by excel.


Gratefully,

 

[prasaddn]

Hi,

I could not understand interval column, and it would be easier if you put the expected output Available time for some of the rows/agents manually, and upload the file.

regards,
Prasad DN

 

[Afarag]

Hello Prsasaddn,

at first the interval mean the hour's quarter, and it's arrived by rounded the "Visit time" or the chat time to nearest quarter

i'm really solve it by this logic

=IF(COUNTIFS($A$2:$A$6913,A2,$D$2:$D$6913,"<="&C2,$E$2:$E$6913,">="&C2+15/1440),0,15/1440-(MIN(SUMPRODUCT(MAX(($A$2:$A$6913=A2)*($C$2:$C$6913=C2)*($E$2:$E$6913))),C2+15/1440)-MAX(C2,SUMPRODUCT(MIN(100*($A$2:$A$6913<>A2)+100*($C$2:$C$6913<>C2)+($D$2:$D$6913))))))

what i was need is to count the avail time per agent at each interval
i uploaded the solved one for any member want knowing how it solved may need to use this logic after that and to share our knowledge.

thanks a lot for your following,