• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dear experts, I have a data sheet with agent names and chats start and end time. What I want to do is to calculate concurrent chats an agent was.

A

Abhishek_8895

New Member
I have a data set that has agent name and chat start date and end date.
I want to calculate that how many concurrent chats and agent with active session

For eg: If a person "A" is on chat at 9:15 am and it continued till 10 AM, in between he got another chat which is at 9:20 am. Now the concurrency should be 2, as he is on dual chat.

Next, if he ends either first or second chat and gets another chat and if that 3rd chat is in sync with my first chat login and 2nd chat has ended up. Then beside the 1st chat, the concurrency should show as 3.

I have tried to make a formula but that is giving #value for some reasons and any help will be appreciated
 

Attachments

  • Sample Data.xlsx
    215.9 KB · Views: 6
Have recent development as now it seems to be working but only for 1 agent gyupta-s, numbers are showing incorrect. Can someone suggest or help
 

Attachments

  • Sample Data.xlsx
    236.1 KB · Views: 4
What version of Excel are you using?
Does it support, for example, formula functions such as FILTER and BYROW?
 
vletm said:
Abhishek_8895
Have You checked this?

Overlapping times a chat agent supported an end user

I want to calculate how many instances every chat agent handled more than one chat (2 or 3 or even 1) at a time and helped end user. The CSV report that I receive has the start (Col C) date, time and end date, time (Col D) of every chat, duration (Col E) and the agent name ((Col P) that...
chandoo.org
Click to expand...
I checked now and seem something similar I have done but for me, its not working..Can you check and help for any errors
 
I'm not sure exactly what you mean regarding concurrency but in the attached on sheet data (2) row 48 in cell Z48 showing 5 concurrent calls for the same agent. Graphically, they look like this:
80200
You can see that the call in row 48 in blue has had 4 other calls overlapping it somewhere (even only a bit in 2 cases), so I've counted this as 5 concurrent calls. [Ignore the x-labels, they're all wrong!]
The sheet data (2) is a copy of your data sheet but I've converted all the text date/time stamps in columns Q and T to real excel dates and times.
See column Z for concurrent call values.
If there is no date/time stamp in any of the cells in those columns those cells should be completely blank, otherwise you'll get a #VALUE! error, sometimes in the whole column in the case of the alternative formula in column AA (same as Z but using table structured references).
You may need to commit the formula to the sheet with Ctrl+Shift+Enter (called array-entering or CSE entering) in a single cell, then perhaps you'll need to copy down in Excel 2016; I'm not sure, you'll have to experiment.

In Excel 2016 you'll have Power Query available, I'll have a go producing a solution with that later perhaps.
 

Attachments

  • Chandoo48484Sample Data_02.xlsx
    430.7 KB · Views: 10
@vletm , careful, your date conversion ignores AM/PM.
Look at the first row (row 2): a 25 minute call starting just before midnight should finish the next day. Yours starts just before midday. Surely this is important when looking for concurrency with other rows?
Easily rectified by changing the formula in column AG from:
=MID([@aa],FIND(" ",[@aa],1)+1,8)
to say:
=MID([@aa],FIND(" ",[@aa],1)+1,15)
 
A Power Query solution in the attached. Textual dates are handled by Power Query.
The orange table is the Power Query output. Right-click it and choose Refresh to update it.
I've added an extra column to the main table to XLOOKUP the values from the PQ table.
The values agree with my previous offering. The whole thing was much easier than the formula, although I haven't optimised the steps in the query.

Instead of PQ outputting a small 2-column table, it can output any subset of columns from the original data and we can ditch the XLOOKUP.
 

Attachments

  • Chandoo48484Sample Data_PQ01.xlsx
    245.1 KB · Views: 12
p45cal
Yes, good point
... I don't use AM/PM at all.
Maybe my 2 row is different than Yours.
Why do You would use 15? ... isn't 11 enough? space and AM or PM .. or how?
 

Attachments

  • Sample Data.xlsx
    347.3 KB · Views: 6
You must log in or register to reply here.
Back
Top