Excel Question #2

[cstclair9]

I manage a call center, and the majority of statistics that I track are times.....

ie. the average talk time for RepA = 1:17 (1minute 17seconds)

Is there any way to quickly format a cell to input stopwatch time, opposed to clock time? Basically all pre-loaded formatting I can find will convert my entry into some sort of clock time, therefore preventing me from utilizing excel for averages, sums etc.

The only work around I have found is to enter the formula =TIME(0,0,A1); I then have to enter the time in seconds in cell A1, and then excel converts it to 00:00 visually.... but it seems to me there has to be an easier way....

 

[SkipVought]

Hi,

"the average talk time for RepA = 1:17 (1minute 17seconds) "
[tt]
0:1:17
[/tt]
faq68-5827



Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[cstclair9]

But that doesn't work? I can format it to that time to get the visual affect I want, yes. But, if I enter 117 into an excel cell and use that time format it will visually appear as 01:17:00. However, excel still recognizes this as clock time. (ie 1:17pm) So because it is clock time I cannot enter additional formulas to calculate averages, sums etc, because excel does not recognize it as stop-watch time, and do the math accordingly.....

 

[SkipVought]

"But, if I enter 117 into an excel cell..."

If you read & understood the FAQ link I posted, you would understand FULLY why 117, with ANY format, can NEVER be a time value in Excel.

Once you understand, the we can talk about your math. Until then, it would be pointless to try.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[xlbo]

If a cell is formatted as hh:mm:ss
and you enter 01:17:00

that is what it will treat the time as

The time you entered is actually the decimal part of the day FORMATTED TO LOOK LIKE A TIME

01:17:00 is actually 0.053472222

Once you understand this, you will understand that it doesn;t really matter what the cell looks like, the matchs is applied to the underlying value...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question