Calculate Average Handling Time, without a timer??

[gringo287]

Hi,

This has a very good chance of making me look silly.. or, at least be a complete non starter.

I want to create a way of enabling users, to calculate their Average Call Handling Time, with little to no more work, that they will already be doing, while using a tracker, I've already created that calculates all their other call related stats.

I've tried this before, by using a timer, but the obvious snag, there is forgetting to "Start" or "Stop" the timer!!

I want to take advantge of the pc clock/ =NOW() function.

I've attached what I've done already and Its actually proved to be quite simple and technically looks like a "goer"...

The snag, is removing, from the total average, the "Stuff" that doesnt count (breaks, lunch, training etc)

My thought, is to add a button to the userform, that would remove the desired amount of time from the total talk time, as for example, the advisor comes off their lunch (1 hour), which would cancel out the 60 minute "call time" difference between the last time they clicked the button from the last call and taking the next call after their lunch.

I'm concious of waffling now, so I'll leave the attachment to fill in the gaps.

Please excuse the look of the example.. It's just a mock up to play around with the idea.

[IkEcht]

What you want to do sounds pretty straightforward but couldn't find what you want on your sample-sheet.

Will all advisors have and use the same amount of lunchtime (and other breaks?). Would just adding a button <break> be out of the question?

[Bob Phillips]

What you want to do sounds pretty straightforward ...

Yeah, until you open the example, and then you think ... WTF :bug:

[gringo287]

Ha Ha.

Yeah, What makes sense to me, often tends to have the opposite affect on others.

I've removed the bits that distract from the task at hand and added some notes to fully explain my ramblings

I've stuck the notes on here as well, but may make more sense if you're looking at the example.


A12 = current NOW()
A13 = Previous NOW()
A11 = Difference between the two

A11 is what I'm treating as the total handling time ( call duration, hold time, after call work etc)

The macro will will keep copying these results down, which will create the overall average. one small bug, that I've not looked into yet, is, that if its been a while between occasions I'm working on it and I click the button twice, it seems to throw out an incorrect amount of time??..

This, so far has been the easy part.

Now there is maths involved.. my brain keeps freezing.

In terms of deducting the "lunch" etc, that wont be a problem, as thats just a case of how I end up designing the userform, so the "button", will do what Its told to do.

My issue is more, how to correctly deduct that time and still keep a realtime view of the average.

in the example of an advisor taking the last call before lunch and then taking the next call after 60 mins - This will mean that there would be for example 68 mins difference between the last call and the next. How do I remove the 60 from the total call time.

[gringo287]

I think I may have sussed it... Watch this space

[khu]

IF I'm understanding this correctly

11642

"Start Shift" clears the form and adds the current time to "Current Log"
"Log time" records the current time, subtracts from the last record, records this in the "Last Difference", makes a record below and updates the running average
"Log lunch" adds a record of a 1hr lunch below and omits this from the "average" calculation

I've named ranges, which I usually don't like to do, but it will help in understanding the math and function of the macros. I was also generous with variables for this reason.

If you're adding other breaks, you could copy the lunch button and sub and adjust the time accordingly. Also change the "if instr(lunch)" under the average sub that omits the lunch entry.


Also not fully tested, so mess around with it and ask if there's something out of whack.

Khu

[gringo287]

Wow!! Khu, you understand me primitive Gibberish!!.

You have pretty much nailed it, thank you very very much.

This may sound ungrateful, but I'm going to have to Store your version, in the "when I Understand it" file.

There's a good chunk of this, that I do understand, but too much that I don't, for me to be happy to pass this out at work as something that I've done.

A lot of this, goes over my head. "Option base 1".. I have no clue :wot


Option Base 1
Sub RunningAvg()


Dim avgarray As Variant


If Range("last").Value = Empty Then Exit Sub


avgarray = Range(Range("logs").Offset(1), Range("logdiffs").End(xlDown))


For i = 1 To UBound(avgarray)
If InStr(avgarray(i, 1), "Lunch") Then
GoTo NextEntry
Else
Sum = Sum + avgarray(i, 2)
End If
NextEntry:
Next


avg = Sum / UBound(avgarray)




Range("avgtime").Value = Format(avg, "hh:mm:ss")
Range("totaltalk").Value = Format(Sum, "hh:mm:ss")






End Sub


As I Think I've got an idea what I need to do now, I'm going to bash my head against the screen for a bit, until I get there and then I'll post what I've done. I hope you take this a compliment and not a waste of your time, as I genuinely am chuffed with what you have done and it very clearly works.

I honestly will, be examining your copy, very closely until I can get my head around it.


Thank you again

[Bob Phillips]

Nobody could take that as anything other than heart-felt thanks :)

[gringo287]

Thank you xld.

I was worrying that, it may be taken the wrong way. Its a shame Its above my skill level, because it is exactly what I'm aiming to achieve, asside from the other potential breaks etc, but there's no way Khu would know what other exception types I would need.

[khu]

Hey no worries, I completely understand! I'm still in your situation on a lot of topics and I would not feel comfortable distributing something I couldn't debug for the recipient. Mull over the program and search the parts you don't understand. Luckily, nearly anything you're not comfortable with yet has been asked by someone, somewhere, and will most likely have a great write up about the function. I've only been using VBA for a couple months now a I frequently have to search for new function that I have not used yet.

One thing to keep in mind is that I have named the cells to aid in seeing the calculations.

I could add some more notes to the workbook to give a little better direction on the specific functions if you'd like, just for reference when building your workbook.

I'm not sure where you are, but here are some general comments for the section you provided:

Option Base 1 is added before the sub and sets the array base to 1 instead of 0. By default, if you populate an array, the first element is MyArray(0). With Option Base 1, MyArray(1) is the first element.



avgarray = Range(Range("logs").Offset(1), Range("logdiffs").End(xldown))

This line pulls the entire range of call logs and durations that have been recorded below into 'avgarray'. I pull in both the time and duration columns so I can later look for the 'lunch' log and omit the value from the average.

It really helps calculation time to pull data into an array and do calculations in computer memory, instead of accessing the sheet and cells for each value. I'm not sure if you'll have enough logs to really matter, but it's a good method regardless.




For i = 1 To Ubound(avgarray)
If InStr(avgarray(i,1), "Lunch") Then
GoTo NextEntry
Else
Sum = Sum + avgarray(i,2)
End If
NextEntry:

Next

avg = Sum / Ubound(avgarray)


This loop goes through the new 'avgarray' starting at the first entry and going to the Upper Bound of the array. The Ubound() function just returns the number of element/entries in the array. This allows the code to dynamically adjust for the changing number of logs.

First, the code checks if 'Lunch' is in the left column (log times) of the array with instr() function (read: is the term “lunch” in the array entry avgarray(i,1)), and if if it is, goes to the 'NextEntry' line, omitting the “Lunch” entry from the average.

If it's a regular log, the code adds the corresponding right column, or duration entry, to the 'Sum' variable, which is the running sum each time a call is logged.

With the running sum of log durations, this variable is divided by Ubound(avgarray), which returns the total number of calls logged, giving an updated avg variable.




Range("avgtime").Value = Format(avg,"hh:mm:ss")
Range("totaltalk").Value = Format(Sum,"hh:mm:ss")


Lastly, the new values for avg and Sum are dumped into the cells named 'avgtime' and 'totaltalk' and formatted.

I find it very helpful to step through the code using the Watches window to monitor variables. You can also use the Immediate window to interact with VBA while you're stepping trough a program.

Some references on these tools:

http://msdn.microsoft.com/en-us/library/f177hahy.aspx
http://msdn.microsoft.com/en-us/library/aa263474(v=vs.60).aspx
http://www.cpearson.com/excel/DebuggingVBA.aspx

Let me know if there's anything else specific you've got questions on while going through this!

khu

[gringo287]

Hi guys,


Sorry for the delay. My job keeps getting in the way of me doing what I actually want to do..


Anyway - I've attached the rough working version. I wanted to make it, as much as possible, using my own work. I've made no attempt to hide my bad habits/unnecessary extra lines of code etc etc. I've had one bit of help off Snb (Here (http://www.vbaexpress.com/forum/showthread.php?49529-How-to-show-percentages-from-multiple-cells-in-the-caption-for-one-userform-button)) to work out, how to show multiple cell ranges for the caption of a command button. I've also liberated, some code from Haluk off Mr.Excel, to allow me to take advantage of the right click menu (http://www.mrexcel.com/forum/excel-questions/97871-need-right-click-menu-userform-visual-basic-applications.html). I adapted this, to remove the actual menu and just make it trigger the expansion of the userform.

There are a few things that need tweaking, but its essentially working.

PLEASE feel free to tear it apart and point out my bad habits etc, as I'm fully aware there is a better way, thanks to Khu.

I've kept the TCHT function, very basic with the use of a spin button. There are to many different types of exception, to waste time and userform space, to cater for all of them, when its very simple to just log each one with the spin button.

It should be pretty self explanitory, in terms of simply logging the calls/sales. To deduct an exception, you just need to right click and again it will explain itself from there. Right click again to close the exception logger.