Running total/cumulative sum of time?

[appreciatethehelp]

Attached is an example document to help elucidate my question:

Running total:cumulative sum of time example.ods

In this document I want to use a formula to make a running total of the time logged. However, I don’t want to convert the hours into minutes; I would like the figures to remain HH/MM/SS.

Obviously when the minutes reach 60, they should be converted into an hour, then reset, and when the seconds reach 60, they should be converted into a minute, then reset.

I would prefer that this could be done with the hours, minutes and seconds divided into 3 cells as shown, but if not possible, then placing all within the same cell would be acceptable.

How can I achieve this?

Cheers.

-EDIT- I have received an answer from JohnSUN that works.
I am leaving this thread open a little longer for the purpose of further discussion, but I am satisfied with this solution.
Will also take a look at some of the other answers when I get more time, thanks to all who have contributed.

Here is the updated document, with the problem solved using JohnSUN’s directions:

Running total; cumulative sum of time example UPDATED W: JOHNSUN’S ANSWER.ods

[Lupp]

At first a question: For what reason are the durations logged in three parts (h, min, s) instead of one standard time value?
As long as you keep time values based on the default unit of 1 day (d) there is not the least problem to get your cumulative sum. You can simply add such values as you would add values of any other kind of quantity.

Ok. You have to enter durations as if they are TOD values.
Most users wouldn’t care or not even regard the difference. In addition you may display the results in a format better appropriate for durations like done by the format code H" h "MM" min "SS" s".

Actually calculating the 3-column-TimeValues cannot be done by a single “formula”. You either need helper columns where the “logged” numbers are converted to default time values and then added. Based on these results you can convert back to seperate results in 3 columns using mathematical functions. Or you calculate the sums starting with the least significant part (s) and looking for the carry yourself. This cannot be done (imo) by the same formula in the three columns. as an example =F1+C2+IF(CURRENT()>=60;-60;0) in F2 (seconds) should do what you want. But as long as you do not dedicate a column to the carry, you need the rather complicated =E1+B2+ROUNDUP((F1+C2-F2)/60)+IF(CURRENT()>=60;-60;0) (or something similar) in E3. …

(Editing:)
See also this demo. (A fluke led me back, and I noticed that I had attached a bad version. The new one should do better.)
Consider to regard the difference between “time” as TimeOfDay and “time” as duration (or as a scientific / technical kind of quantity). The difference is essential, though often ignored.
Science strongly prefers to express time values in one single unit per application case: Either only seconds (s) or only years in practise, rarely hours.

The mixed format used for TOD (and angles) goes back to an about 5000 years old tradition to subdivide and to bunch by 60 which was abandoned for most applications also some millennia ago. Why? It’s simply too unhandy as soon as you need real calculations.

[mikekaganski]

While I agree with usefulness of the single-unit approach, I wanted to mention that the different units aren’t something abandoned in most applications, as well as something conceptually alien. First, the positional number representation itself is the sample of such concept (each digit place representing its own “unit” - in decimal, it’s singles, tens, hundreds etc); second, there are a lot of places like heights (feet+inch) or weights (lb+oz) etc which are common now.

[mikekaganski]

Another (and much more important) thing to note is that the help in LO is created by volunteers, just as LO itself, and just like this ask’s answers. So, each time you add an answer that you think worth of being reflected in help, and don’t actually propose a patch to help, it’s a sad thing happening.

[Lupp]

@mikekaganski: Being a developer you surely know that first of all functions need be clearly specified. The next step must be a correct implementation. With respect to the TIME function problems start here. The specification explicitly states in its last paragraph: "Hours, minutes, and seconds may be any number (they shall not be limited to the ranges 0…24, 0…59, or 0…60 respectively).
The implementation obviously ignores this. I wouldn’t ennoble the bug anchoring it in the help.

[Lupp]

@mikekaganski (again): With respect to some statements of yours above, it might be preferable to have a private exchange. Since this site does not offer PM, I might (mis-?)use the mailing via the bugs site if you don’t object. My true name is Wolfgang Jäger, and I am known by it to the https://bugs.documentfoundation.org/ .

[mikekaganski]

@Lupp: my email is mikekaganski@hotmail.com; I’d like to discuss whatever you’d like to

[JohnSUN]

You can use a simple formula for converting the hours-minutes-seconds into one value and format this value as desired

[appreciatethehelp]

I formatted the “running total” F column cells using the time format shown in your answer.

I then tried using the formula you gave for cell D2: “=A2+(B2+C2/60)/60)/24”, but in doing so was confronted with this message:

“LibreOffice Calc found an error in the formula entered.
Do you want to accept the correction proposed below?
=A2+(B2+C2/60)/60/24”

When “no” was selected, I got an “Err:508” message. When “yes” was selected, the result was 96:38:14

-continued below-

[appreciatethehelp]

I am not sure why I got a different result to you. So, instead of using a formula for cell D2, I tried typing the time in manually: 04:38:14.

I then used the other formulas you gave for cells D3 and D4, which worked perfectly.

You have solved my problem, thank you very, very much.

Will leave this thread open for further discussion regarding the discrepancy between my result in cell D2 and yours, however I am pretty satisfied with the outcome.

[mikekaganski]

Please check the @JohnSUN’s answer closely: there is an opening parenthesis before A2 that you missed.

[JohnSUN]

I don’t see in your variant of the formula the first opening bracket before A2: =(A2+(B2+C2/60)/60)/24

[appreciatethehelp]

Whoops, I must’ve had my eye on the formula in E3, of course there’s an opening parenthesis!
Sorry fellas.

Aaaand JohnSUN’s answer worked perfectly. Have updated my question accordingly.

Just one more question for JohnSUN though, when you say “format this value as desired”- how exactly would I go about spreading the converted time in the “running total” column into 3 separate sections, similar to the “TIME (reading)” column?

[JohnSUN]

You can use functions =MINUTE(D2) and =SECOND(D2) for additional columns. With the amount of hours the formula is a little more complicated =INT(D2)*24+HOUR(D2) (or just format cells in column D with code [HH])