Excel adding time

  Bee Jay S 17:54 11 Sep 05

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 05

Format as Custom [h]:mm

  Bee Jay S 18:01 11 Sep 05

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 05

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 05

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.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…