Call Center Talk Times

[Randy Rich]

I am struggling to get a column of talk times from our call center to total
up. The format is 0:00:05, for example, and represents the output from the
phone equipment. The report shows the agent name, and the total talk time.
I'm interested in getting a total off all the time all the agents spent on
the phone, and I'm stumped. It just won't =sum(b2:b248). It will do
=b2+b3+b4, but that is going to be difficult when the report is several
thousand entries long. I can use a different column, and get running totals,
but I am puzzled why I can't just get a total from the column. Does anyone
have any insight into the workings of Excel 2007?

 

[Barb Reinhardt]

It's possible that you are trying to sum things that aren't numbers. In an
adjacent column, put something like this to test

=ISNUMBER(A1)

 

[PCLIVE]

What result are you getting? Perhaps some of the values in column B are the
result of a formula. If there are any errors in the column, then your SUM
formula may also produce an error. There may be other ways to test column B
for errors, but here's one way.

=SUMPRODUCT(--(ISERROR(B2:B248)))

If the result is greater than zero, then there is that many cells in your
range that result in an error.

Does that help?
Paul

 

[Randy Rich]

I get a column of TRUE every time I test.

 

[Randy Rich]

Thanks for that test PCLIVE, but my result was 0. I've got to believe it is
something to do with the data format, but I honestly can't tell what it is.
I've tried a bunch of different cell formats, hoping each one will give me
the total I need. I end up with something that says 12:00:00 AM or something
similar. I even ran a pivot table, trying to get it to sum that way. One
agent got a 5 second total. Everyone else was zero. I tried to find out
which entry at least got me a 5, and from what I can tell it was the one I
entered by hand. However, it seems to work flawlessly when I do the
additional column and get the 'running total' - so selecting individual cells
seems to work. Highlighting the column gives me the 'count' at the bottom of
the page, but not the sum or the average. It's frustrating.

 

[Tim Otero]

Randy,

In order to find the cell, or cells, causing the problem, try
highlighting the column, starting at the top, until there is no sum in
the status bar. Investigate the difference between the cells giving a
sum and those which do not.

HTH

tim

 

[Randy Rich]

Thanks Tim, As it turns out none of them give me a sum and the bottom. My
'count' increases, but at no time does a sum ever appear. I've been reading
about others who have a similar frustration, and one user just copied the
column of numbers into notepad and pasted them back in. I'm going to try
that next.

 

[Randy Rich]

Hi everyone, I'm so grateful for your insights and ideas. As I've been
reading other posts in this forum, I found one user who copied out the column
of data into notepad and then pasted it back in. I thought I'd try that too,
and for some reason that works just fine. That gets me out of my puzzle
temporarily, but I'm sure that I'm going to get similar reports out of this
equipment - and this approach seems an onerous way of getting my results.
I'd sure be interested if someone can identify what the real challenge is so
this step can be avoided in the future. Any MVPs have the needed insight?

 

[ward376]

If you'd like, send me a sample and I'll get back to you.

Cliff Edwards

 

[Barb Reinhardt]

I'm beginning to wonder if you are getting sums of times (in time format) and
haven't converted them to hours by multiplying the sum by 24.

 

[Pete_UK]

I think the data you have is made up of text values that just look
like numbers, and perhaps you have some spaces and/or non-breaking
spaces in there. If you still have the offending data perhaps you can
do:

=LEN(A1)

on some of the cells to see how many characters are in the cell.

One way around it within Excel would be to use Data | Text-to-columns
and specify date formats. Another might be to add zero or multiply by
1 through Paste Special. A third way would be to use the formula
=VALUE(A1), assuming your offending times are in column A.

Hope this helps.

Pete

 

[Barb Reinhardt]

So what was the problem?

 

[ward376]

I just converted them to values...

Sub way()
With Sheet1.Columns("ab")
.Value = .Value
End With
End Sub

Which worked fortunately - I've seen lots of imports that require more
finagling to get time values that you can work with in xl.

Cliff Edwards