Call Center Spread sheet

[merrellpa]

I work as tech support and i would like a Excel spreadsheet that is pretty simple but does a few things.

the main thing i cant figure out is how to add a time stamp.

the spreadsheet is pretty Basic.

Column 1: Account #
Column 2: Time call started
Column 3: Time Call Ended
Column 4: Total Time on call


I know how to enter in a account number

But is there a way so that as soon as i enter a account number it will stamp the time into Column 2?

The second part is if i could add a Button or something that would allow me to add a time stamp in Column 3. that way when i am done it will automatically enter the time for me.

Calculations for the total time i can do.

 

[shemayisroel]

Hi merrellpa & Welcome,

To clarify you are wanting to enter an Account Number in Column A, then when you finish this you want an automated time to be inserted into column B - this becoems your start time?

Then have a button in Column C that you can press to enter a time stamp - this becomes your stop time?

In Column D you'll have a formula to work out the total transaction time?

If yes then you'll certainly need some sort of VBA solution.

I'm a little surprised as most call centre CRM systems already have these types of solutionsbuilt in as most call centres track anything and everything like AHT, ATT etc

With the time stamp did you want it to ddd dd/mm/yy hh/mm AM/PM formatt?

 

[lenze]

Private Sub WorkSheet_Change(Byval Target as Range)
If Target.Count > 1 Then Exit Sub
If Target.Column > 1 Then Exit Sub
Cells(Target.Row,2) = Date
End sub

lenze

 

[merrellpa]

shemayisroel yes thats exactly what i want to do.

But yes, we do have systems built into the phones and whatnot that do reports. but not something i can see in real time. Managers can, but try asing them what your AHT is every couple of hours lol.

Lenze that looks cool... but treat me like i answer phones for a living No programming knowledge. I have no idea what to do with it or where to paste it

 

[shemayisroel]

shemayisroel yes thats exactly what i want to do.

But yes, we do have systems built into the phones and whatnot that do reports. but not something i can see in real time. Managers can, but try asing them what your AHT is every couple of hours lol.

Ok thanks for confirming, hahah I know what you mean as in my previous roles I managed a couple of call centres

Well maybe they need to change the access levels to allow you access to your own live stats...

By developing this spreadsheet it will only add to you AHT as there is now an extra step you need to do to complete the call...

 

[merrellpa]

prolly true... but a second or 2 is fine... the big thing is we have been having trouble with our phone systems lately and At the moment i'm having to write down everything i want the Spreadsheet to do anyway... and doing it manually adds A LOT more to my AHT than if i could get it to do it automatically.

 

[Sandeep Warrier]

Hello,

To try out Lenze's code, right click on the Sheet 1 tab that you can see on the bottom left part of excel and click on view code. An editor will open up and you can paste the code there. Click save and close the editor.

To use Macros you may have to change your security setting. In Excel go to Tools-> Macros -> Security & Select Medium. Click OK and restart excel. You will get a tab asking whether you would like to enable macros or disable them. Select enable macros. Just type in the account info in cell 1 and the adjacent column will be automatically populated.

 

[lenze]

Thanks, Saneep for answering. I did, notice, howvwer, I made an error in the code . merrellpa wanted time , not date. So the code should be

Private Sub WorkSheet_Change(Byval Target as Range)
If Target.Count > 1 Then Exit Sub
If Target.Column > 1 Then Exit Sub
Cells(Target.Row,2) = Time
End Sub

Also, you can use a BeforeDoubleClick to insert the Time the call ends in Column 3

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 3 Then Exit Sub
Cancel = True
Target = Time
End Sub

lenze