• 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.

Need to find concurrency or frequency of an employee who is occupied with Chats

Hi Team,

I am trying to get the Chat concurrency based on the start and end time stamps. However, I am missing some logic here.

Here is how the concurrency should be given.

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.

Attached is the file for reference
 

Attachments

  • concurrency demo.xlsx
    9.9 KB · Views: 24
Rajashekar Reddy
Until You'll get 'formula-solution', You would test this
... press the [ Button ]-sample.
(( Changed to file which do not have results ready ))
 

Attachments

  • concurrency demo.xlsb
    17.7 KB · Views: 17
Last edited:
Rajashekar Reddy
It's without 'the formula'.
How did You test it?
Was there results ready?
Is same data then same results ... or how?
If so, then clear those results and press the button again.
 
Well, I am expecting the result using Formula. If I had the formula then I would not have posted it here in the Forum.

I am those numbers using formula to get the concurrency numbers.

Thanks in advance.
 
I did that and it is working.

The button works absolutely fine when I change the data and check for the concurrency

Can you please help me with the steps on how did you do this
 
Rajashekar Reddy
As You noticed that is not formula-based.
You would check that macro and check how do it (try to) work.
Basic idea was - check row by row is there overlaps with other rows.
 
Rajashekar Reddy
a) You could use that file - copy Your needed data as in sample and press the button
b) Many things can do - as I wrote ... while waiting ... I won't do that kind of formulas.
 
This formula counts the number of starts to date and subtracts the count of chat ends.
= COUNTIFS( [Unique Name], [@[Unique Name]], [Start time], "<="&[@[Start time]] ) -
COUNTIFS( [Unique Name], [@[Unique Name]], [End time], "<="&[@[Start time]] )
 

Attachments

  • concurrency demo (PB).xlsx
    17.2 KB · Views: 15
Thanks Peter.

I've looked at the sheet with the formula mentioned in Excel file. However, the cells which are formulated by you should give an output which is shown in Column "E".


This formula counts the number of starts to date and subtracts the count of chat ends.
= COUNTIFS( [Unique Name], [@[Unique Name]], [Start time], "<="&[@[Start time]] ) -
COUNTIFS( [Unique Name], [@[Unique Name]], [End time], "<="&[@[Start time]] )
 
Sorry, I misunderstood your definition of concurrency. The new file is based upon the number of chats that overlap the current chat irrespective of whether they occur simultaneously or in sequence.

= COUNTIFS(
[Unique Name], [@[Unique Name]],
[Start time], "<="&[@[End time]],
[End time], ">="&[@[Start time]]
)
 

Attachments

  • concurrency demo (PB1).xlsx
    18.1 KB · Views: 21
Back
Top