Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 22 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.