extract call workload in 15 minute increments

[bradja]

Hi All,

I have recently started to write some vba/macro's to assist with calculating information at work to make my job easier. As such I do not know best practice and have just been managing to get stuff done through tedious trial and error until I find something that works.

I have a csv report that shows what the phone activity for all of our staff in our call centre.
The report has [Agent Name], [AgentState], [StartTime], [EndTime] columns. (It has other columns but I am not concerned about those)
For example.
Homer Simpson, WaitForCall, 08:00, 08:01
Homer Simpson, CallInbound, 08:01, 08:16
Homer Simpson, CallOnHold, 08:16, 08:18
Homer Simpson, CallInbound, 08:18, 08:19
Homer Simpson, WaitForCall, 08:19, 08:20


What I want to do is display current Workload (in seconds) for each 15-minute increment so I can see how we tracked throughout the day.
Workload is only CallInbound and CallOnHold time - not WaitForCall etc.
For example.
08:00 - 20,000
08:15 - 45,000
08:30 - 63,000
08:45 - 101,000

Now I am trying to figure out how to do this the best way without writing repetitive code for each and every 15-minute increment.
Now I don't know if my code below will make sense so please do ask questions if it will help to clarify.
Also if anyone has any advice or suggestions I am definitely keen to listen as I want to learn to do these kind of things so they run efficiently as well because I am working with >10,000 rows of data usually. Also if it's better to have an example spreadsheet attached let me know so I can create something to attach.

So far I have this (And I am sorry if it looks badly written as I am only learning this type of thing recently)

Please Login or Register  to view this content.

[FDibbins]

"Hi and welcome to the forum

We would love to help you with your question, but 1st, in accordance with forum rules, please rename your thread to something more meaningful, that actually describes your problem.

Because thread titles are used in searching the forum it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title used as search terms would return relevant results.

Many members will look at a thread title, and if it is of interest to them, or it falls within their area of expertise, they might only open those threads.

Look at it this way...if you typed that title into google, what would you expect to get back?
To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title"


Also, I fixed your code tags for you

[bradja]

Thank you FDibbins I hope the new title is appropriate

[bradja]

I have created an example file of what I am working on that might help to understand what I am doing.
Workload.xlsm

[nilem]

Hi bradja,
try it

Please Login or Register  to view this content.

[oeldere]

Or how about an pivot table. (not what you asked, but it give you, maybe an new vieuw).

[bradja]

Hi nilem,

Your code does exactly what I needed.

Thank you so much it is greatly appreciated.

Could I please ask, Dim y(46, 1) As Date, i& - What does the "i&" part mean?
As I understand the first part is setting up a 2-dimensional array but I do not understand the last part.


Regards,

Bradja

[bradja]

Hi oeldere,

Thank you for your suggestion. At this stage I do not know anything about Pivot Tables so I will use your example as a good start for learning what I can do with them :D.

Your help is very much appreciated and I am very thankful!


Regards,

Bradja

[nilem]

Hi Bradja
Dim i& the same as the Dim i as Long