I need to calculate the interval between phone calls for agents working in a call centre.
Example: An agent places a phone call at 10:00 AM with a duration of 3 minutes. They make their next call at 10:10 AM. The time between the end of the first call and the beginning of the second call equals an interval of 7 minutes. This is a straightforward calculation when dealing with call records for just one agent but more difficult (for me) when the data contains call records for multiple agents combined. My attempts using VLOOkUP and INDEX(MATCH have been unsuccessful thus far. Any assistance is much appreciated!
I have attached a sample sheet of some data, which is also pictured below. I have highlighted the formula which subtracts the start time of the previous call from the current call, less the duration of the previous call, to correctly arrive at the interval between calls, however, the formula does not account for the name of the agent so I have to adjust it manually for each call record which is what I'm trying to avoid.