LibreOffice SUM time period in format HH:MM:SS

Question

I want to obtain the sum of several cells containing a period of time in the format HH:MM:SS. In LibreOffice Calc 4.0.3.3, I've copy-pasted the periods of time in the range G14:G21, and formatted the cells as Time HH:MM:SS. I formatted in the same way the cell G22, and inserted in it the following SUM function:

=SUM(G14:G21)

Instead of the expected result, the function returns me this solution 00:00:00. Could you explain me where am I wrong?

Ithaman

Answer 1

You have the wrong format. HH wraps around every 24 hours. Try using [HH] instead of HH, then you get the total number of hours.

Crockett

Answer 2

The problem was on the way the data were inserted. Even if cells were formatted as Date, analysing them more in detail I found the values were preceded by an ' (apostrophe). Thus means LibreOffice were seeing them as text string and not a period of time. Deleting the apostrophe solved the issue.

Ithaman

Answer 3

Seems to be no answer - here we go: Formatting the cell(s) [HH]:MM as user defined will resolve your issue. Just had this problem, creating a time record sheet for my wife....

Confidence

There were actually three other answers, and I believe one of them covered your response. – Variolite

And the response is "format them yourself manually?" well, not really a a professional sounding solution, I mean from LO, not from you guys. I think I will stick to Excel for now :) – Teenybopper

Answer 4

Is a special function required to sum a range of hours and minutes? I'm using 6.2.0.3 on macOS and SUM doesn't calculate the value correctly. Oddly, however, if I select all of the cells in the range then the correct value is displayed in the footer bar does. But, for example, SUM(G1:G15) doesn't show the correct value.

Fragmentation