Link to home
Start Free TrialLog in
Avatar of mmoniz
mmoniz

asked on

need help finding out number of concurrent calls within excel

Hi Experts, I am needing to find out my maximum number of concurrent calls at any time of the day, I think I am close with the attached sample but I need help getting the sumproduct formula to calculate how many active calls there were for that particular second of the day.  A sample below is for 09/30/14, I am thinking once I have this formula I can do a new sheet per day and populate the start time and end time then it will let me know the most concurrent calls I have had within the entire month.
I need the proper formula in the concurrent call column so I can copy it down the sheet and populate all seconds of the day.

Thanks so much in advance for any assistance you can provide!
Call093014-WORKSHEET.xlsx
SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mmoniz
mmoniz

ASKER

looks like that may work, I tried applying that formula to my sheet but it does not show any calls calculating.  attached is what I did trying to match your sheets formula to what I have populated.

the end result I need to get too is for every second of the day(column H) how many active concurrent calls there were based on the start time (column A) and the end time(column B or C)

I attached my attempt to apply your COUNTIF formula in case you can see what I am missing to find the result I need.

Thanks again,
Mike
Call093014-WS-Countif.xlsx
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mmoniz

ASKER

Awesome, Thanks so much for your help guys, that is exactly what I needed!  Much appreciated!
Thank you  Glenn,

I used function of =TimeValue  to convert the COlumn A and B with the correct time values and then it worked. is that what you did exactly?

or you had a better way to do it, as usual
Avatar of mmoniz

ASKER

IT is strange when I try to do the same it does not work, I highlight both columns A and B and I right click and select Format Cells then I select Time and the same option you have in the sheet you sent 13:30:55, it still does not work and change it to a time value with AM or PM showing in the cell line.  I see the AM and PM designation in the sheet you uploaded but cant seem to get my values to convert in columns A and B, am I missing something?

Thanks again for your help!