Check if range of time falls in another range of time and get difference

Hi Excel masters!

I'm still new to Excel and have been trying to figure out on how to automatically check the range of time if it falls under another range of time on a certain date and have the time deducted if it does.

I have attached a sample where I omitted some data due to our company policy.

A good example would be if:

Date: 6/1/2018
Idle Time: 12:00 to 12:13
Received a call: 12:03 to 12:11

Our system will show that agent was idle from 12:00 to 12:13 (13mins) but if he received a call from 12:03 to 12:11, it shouldn't be considered as idle for 13mins but should show as idle for only 5mins

-Another example is:

Date: 6/2/2018
Idle Time: 12:00 to 12:13
Made a call: 11:55 to 12:03

Again, system will show that agent was idle for 13mins but since he made a call from 11:55 to 12:03, we need to take the 3 mins difference made from 12:00 to 12:03 so that the agent will appear as idle for only 10mins instead of 13mins.

Currently, I have set a productivity report that calculates (ticket time + inbound calls) divided by login hours since most of the time, if the agent receives a call, he will not be working on a ticket in which sometimes they do so our productivity report isn't even at 90% accurate. It should be as (ticket time + total minutes of calls for inbound & outbound (deducted from the idle time only)) divided by login hours.

I figured out on how to get the overlapping time using formula and adding extra column:

Please Login or Register  to view this content.

My only problem is how to align the call time (dates) vs ticket time (dates)

I finally figured out on how to align the times with date and everything looks fantastic. Unfortunately, I had to use concate which created a lot of unnecessary columns. Still, I got what I wanted to see. Unfortunately though, my formula only works on one employee, so I might ask on how to make it work for a lot of data on multiple criterias