Excel adding time

  Bee Jay S 17:54 11 Sep 2005

I have a column in a spreadsheet which is formatted as time (h:mm), when I try to total the column it is not returning the correct sum. The total is formatted as 'time'.
Can anyone help? Thanks

  VoG II 17:55 11 Sep 2005

Format as Custom [h]:mm

  Bee Jay S 18:01 11 Sep 2005

VoG - thanks for response one minute!!
I have also tried formatting the total as (h:mm)
but it returns a result such as ) 0.30 when the answer is obviously several hours. I wondered it something happened if it went over 24 hours?


  VoG II 18:04 11 Sep 2005

Excel expects the result to be a time as in the 24 h clock. If the result is over 24 hours it 'loses' whole days to produce the result it expects.

That's why you have to set tha Custom format of [h]:mm.

  Simsy 09:26 12 Sep 2005

You must enter the time using a colon to seperate the hours:minutes:seconds (depending on which components of time you are using)

VoG™'s comment is correct. To rephrase it slightly, there is no such "time" as 25 o'clock, which is what you're trying to achieve by formatting as "time". What you seek is a "duration". as VoG™ siad, format the cells using a custom format;

15 hours plus 10 hours will give the followiing results...

h:mm equals 1:00

hh:mm equals 01:00

[h]:mm equals 25:00

[hh]:mm equals 25:00

depending on you exact calculations,you may aslo find that you have to either multiply of divide your figures by 24 to convert to/from days to hours.

The following 2 sites, (2nd courtesy of vog), are excellent sources of info and explanation for time calculations;

click here

click here

I hope this helps,



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?