How to calculate time between login and logout times?

Hi,

I am running a service desk which is required to operate between certain hours. I want to check my staffs login and logout times over the week and calculate if they owe any time.

Here is an example piece of data that report produces (Time, Agent, Type, Description):

02/09/2022 10:37 Agent 1 phone Agent Answered
02/09/2022 10:37 Agent 2 phone Agent Disconnected
02/09/2022 10:37 Agent 2 phone Caller Disconnected
02/09/2022 10:37 Agent 3 phone Agent Disconnected
02/09/2022 10:37 Agent 3 phone Caller Disconnected
02/09/2022 10:37 Agent 1 phone Attempted Call To Agent
02/09/2022 10:35 Agent 4 phone Call Wrapped Up
02/09/2022 10:35 Agent 5 Agent Logged Out
02/09/2022 10:35 Agent 5 Agent Logged Out
02/09/2022 10:34 Agent 6 phone Connected Caller To Agent
02/09/2022 10:34 Agent 6 phone Agent Answered
02/09/2022 10:34 Agent 6 phone Attempted Call To Agent
02/09/2022 10:34 Agent 4 phone Agent Disconnected
02/09/2022 10:34 Agent 4 phone Caller Disconnected
02/09/2022 10:34 Agent 1 phone Call Wrapped Up

So I need to first dig out each agents login and logout instances from column D, then I need to calculate the difference between the login and logout time for that day from column A. The agent should be logged on from 8am until 4pm, or from 10am to 6pm.

Is this possible with this data or is there too much work involved to get this?

Kind regards,
Adam

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Hi Ali,

Many thanks! I have uploaded an example of the data I have now as an attachment and done another table on the right of it explaining what data I would like to grab from it.

Hope this helps!

Kind regards,
Adam

As modeled in G7:J13
1. Cell G7 displays the agent being audited using data validation: =OFFSET($M$2,0,0,SUMPRODUCT(--($M$2:$M$4<>"")))
Note that the list of agents is in cells M2:M4 which are populated using: =IFERROR(INDEX(B$2:B$14,MATCH(0,INDEX(COUNTIF(M$1:M1,B$2:B$14),,),)),"")
2. Cell H7 is populated using: =COUNTIFS(B2:B14,G7,D2:D14,"Logged In")/2
3. Cells G9:G11 are populated using: =IF(ROWS(G$9:G9)<=H$7,G$7,"")
4. Cells H9:I11 are populated using: =IF($G9="","",AGGREGATE(14,6,$A$2:$A$14/($B$2:$B$14=$G9)/($D$2:$D$14=H$8),ROWS(H$9:H9)*2))
5. Cells J9:J11 are populated using: =IF(G9="","",MROUND(I9-H9,1/1440))
6. Cell J13 is populated using: =SUMPRODUCT(--(G9:G11<>""))*8/24-SUM(J9:J11)
Note the formatting for cells J9:J11 and J13 is h "Hrs "m "Mins"
Let us know if you have any questions.