time formulas

  • Thread starter Thread starter axiom
  • Start date Start date
A

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
 
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!
 
Back
Top