time formulas

[axiom]

Can anybody give me an advice

Lets say i put in first column dime of departure and in second one time of
arrival (of course i know have to insert the time ). In third column i get
the time spent somwhere. So far everything is ok. The problem occ urs when i
want the sum of all differences in the third column (rows are months
dates). The autosumm formula wont work and the result is completely wrong.


thanks
miro

 

[Frank Kabel]

Hi
format the resulting cell with the custome format
[hh]:mm

 

[Norman Harker]

Hi Miro!

Just to add to Frank's reply.

The sum is not "completely" wrong but is right! It is only formatted
in a way that is not very friendly.

Excel treats times as the integer part of the date / time serial
number system whereby dates are the number of days since 31-Dec-1899.
The time in any particular day is a decimal part of one day.

Your time difference of (say) 6:00 is recorded by Excel as 0.25 and if
you format as time hh:mm you get to see this number in the form 6:00.
However, if you format dd-mmm-yyyy hh:mm you will see 1-Jan-1900 06:00

When you add times and exceed 24 hours the serial number goes above
one and a "simple" time format will only display the integer part of
the result.

So if you add 09:00 and 21:00 the result in format hh:mm will show
06:00.

But the result in full format dd-mmm-yyyy hh:mm will be 2-Jan-1900
06:00

Formatted as general or numeric will show 1.25

So the result you are seeing is right. And as Frank has said so
succinctly, you can see the time in hh:mm only by formatting [hh]:mm

These principles of time formatting in Excel are important when it
come to (eg) multiplying by hourly rates. 06:00 multiplied by $10 is
$2.50 because 06:00 is 0.25 and 0.25 * 10 is $2.50. You have to use
YourTimeLapse * 24 * 10.

If I'm teaching my Grandmother to suck eggs, Sorry Gran!