Visualising Telephone login sessions per 15 minute

[Sataz]

Hello,

First post/long time lurker!

I want to visualise the amount of time a telephone Agent has been logged in, per 15 minute interval.
The below example shows the time an Agent has been on the phone, went to a break and then returned on the phone.

I've got about 95% of the way there with multiple IF statements, however this fails when there are multiple logins per interval.
I'm looking for something along the lines of "the sum of time between Logon/Logout which falls during Interval X"




[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Status[/TD]
[TD]Login[/TD]
[TD]Logout[/TD]
[TD][/TD]
[TD]Intervals[/TD]
[TD]08:00[/TD]
[TD]08:15[/TD]
[TD]08:30[/TD]
[TD]08:45[/TD]
[TD]09:00[/TD]
[TD]09:15[/TD]
[TD]09:30[/TD]
[TD]09:45[/TD]
[TD]10:00[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]Login[/TD]
[TD]8:00[/TD]
[TD]9:05[/TD]
[TD][/TD]
[TD][/TD]
[TD]00:15[/TD]
[TD]00:15[/TD]
[TD]00:15[/TD]
[TD]00:15[/TD]
[TD]00:05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Break[/TD]
[TD]9:05[/TD]
[TD]9:20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]00:10[/TD]
[TD]00:05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Login[/TD]
[TD]9:20[/TD]
[TD]10:05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]00:10[/TD]
[TD]00:15[/TD]
[TD]00:15[/TD]
[TD]00:05[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Many thanks!

 

[Lordy2501]

Hi,

If I am understanding correctly what it is you're asking, then I think the formula you need is the following....

=text(B1-A1,"h:mm")

This is assuming the start time is in cell A1 and the end time is in cell B1


Hope this helps!

 

[Sataz]

You're way off sorry,

The times underneath the intervals (08:00, 08:15, 08:30 etc) are what I'm trying to calculate. These are based on the login/logout times on the left, but only show the amount of login time DURING each interval e.g. between 08:15:00 and 08:29:59