Excel Time Problem

[Frank Young]

I exported a file from a telephone switch reporting system as a "tab
separated text" file, because when I export the data in Excel I have to
deal with merged cells and other formatting problems.

I have a couple of columns that have time values like 160:02:05 for
logged in time, and 34:16:46 as the waiting dime… Both are time
“DURATIONS” or time in a state.

I used the Format, Cell, custom and [ss] to get the duration value to
seconds, and I get 123406 and 576125 respectively.

I then use these numbers in a calculation typically like 1338 / (576125
- 123406) and I get 219 as the answer.

The problem occurs if I do the same calculation on a calculator. The
same formula 1338 / (576125 - 123406) gives 9.14 as the answer...

I know that the 9.14 is in the right range for my answer.
How do I get excel to give me the right number.

Note: all of the other Duration values that I am using in my
calculations match the calculator answer. This is the only one that does
not!!


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software! Free Support at
http://www.ozgrid.com/forum/ **

 

[JE McGimpsey]

First, I have no idea how you get

=1338 / (576125-123406)

to equal 9.14. On any calculator I use, I come up with 0.0029555 or so.

However, you seem to be saying that you expect the format to affect the
calculation outcome - it doesn't. 160:02:05 is stored internally (in
fractional days) as 6.668113426 and that value will be used in
calculations regardless the number format. Likewise 34:16:46 is stored
as 1.428310185 so

=1338 / (6.668113426 - 1.428310185)

is equivalent to

= 1338 / (5.239803241) ==> 255.3530998

 

[Alex Delamain]

The problem is that changing the format to seconds does not change th
value that excel holds. In the case of 160:02:05 it actually sees tha
as 6.66811342592593 days.

so if you want to convert to seconds for calculation you need t
multiply your values by 86400 to convert the days into second