HR MIN Format...Unable to Calculate

U

usg4614

I'm working on this spreadsheet that contains call duratio
information. In one column it appears that everything if formatte
like this...

1/1/1900 9:54:00 AM-but the value is or should be 9:54 (at leas
that's how it appears)

however, in certain cells within that columns there is data that show
up like this

22691:06

There's not formatting and I'm unable to convert it or add it in an
kind of way.

Basically, Is there a way I can add up all this time into one number?
Do I have to convert it? If so, to what? Why is it that some will no
convert (is it because it's too much)

My ultimate goal is to get a tally of the total hours and mins fo
these customers.

Please help if you can.

Joyc
 
D

Dave O

Part of the answer is how Excel deals with dates and times. In Excel,
a calendar day has the value of 1. In any cell- call it A1- you can
enter the formula =Now() and the current time and date appears. In
cell B1 you can enter the formula = A1+1 and the same time appears
with tomorrow's date.

Excel is interpreting your call duration example, 9 minutes 54
seconds, as 9:54 AM on 1/1/1900. If you go to the cell that displays
1/1/1900 9:54:00 AM and reformat that as a number, the displayed
result is 1.4125. Essentially, Excel has confused your elapsed time
with a timestamp.

How you resolve the problem depends on a couple things: are you
importing this information from a report?

As for 22691:06, that's a stumper, and also makes me wonder if you're
importing this from some type of data file.

I did a ton of this type of work with a phone company some years back,
so I'm curious to know: is this a telephony application?

Dave O
 
M

Myrna Larson

A minor point re the example in your reply (which may or may not be relevant
to the OP's problem): you're off by 24 hours.

If the call duration is 9 minutes, 54 seconds, and you clear the cell
formatting, the value is 0.4125 rather than 1.4125; formatting as a date and
time, you see (the impossible date) 1/0/1900 9:45 AM rather than 1/1/1900 9:45
AM.
 
Top