Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'd like to take a group of cells in the HH:MM:SS format, and add them up. I don't want to have the value wrap on the 24 hour clock. I just want an absolute total of the values. (The sum function seems to wrap by default).

So if I have:

20:00:00
20:00:00
00:10:00
00:00:10
00:00:10

I want to end up with:

40:10:20

A contrived example, but it gets across what I'm looking for.

share|improve this question
up vote 25 down vote accepted

It's a matter of formatting the cells. You will have to apply the format code [HH]:MM:SS. It's already predefined:

FormatCells

(Screenshot from LibreOffice 3.4.3)

share|improve this answer
    
Thanks tohuwawohu - I had tried the formatting that was 13:37:46 and that didn't work. The 876613:37:46 was exactly what I needed. Thanks for the extra-clear screenshot too. – mtyson Feb 7 '12 at 17:42
    
13:37:46 automatically restricts it to 24 hours, because it represents the 24-hour time-of-day format. – Joe Z. Mar 18 '13 at 2:50
    
Works for me. But I found another problem in my sheet: I had a time range from 23:40-00:24 (yes, should not work night shifts). Only when setting the format like that, I saw the negative value which messed up the sum. – Alex Oct 18 '15 at 10:59

Very often the time value is presented as a string ie. appears as '01:00:00 when formatted as time.

There are two solutions to this:

  1. To delete the single quote. This is extremely tedious if there are more values

  2. To create another cell which uses a formula to convert to timevalue. If eg A1 contains '01:00:00 then create another cell with =TIMEVALUE(A1), the formula can be dragged along to easily convert multiple values

share|improve this answer
    
Just an addendum to 1.: you can delete the single quote using a regex search / replace. – tohuwawohu Jun 17 '15 at 5:57

Another solution that I have used is to enter 01:00 AM as 25:00 it still displays as 01:00 but the math is then performed correctly.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .