Summing time durations (NOT times)

[ericjswrycan]

The closest I can find to my question is this Summing Hours as duration not correct but it is not quite the same, and the solution does not work for me.

I want to sum a column of time durations. A time duration is not the same as a time, even if it looks similar and obeys some of the same rules. It seems to me there should be separate format for durations. Using the time format like
[HH]:MM:SS it gives me a result like -00:21:18 even though none of my value are negative.

(I tried omitting the hours entirely, as I have values in minutes and seconds and don’t care about converting up to hours, but could not get it to even interpret a number like 13:38 correctly).

Is there any way to do this?

[erAck]

Format for clock time is HH:MM:SS which displays from 00:00:00 to 23:59:59 and wraps to display 00:00:00 at 24:00:00, similar for MM:SS, even if the underlying timevalue is greater than one day.

Formats for duration are [HH]:MM:SS or [MM]:SS or even [SS] that do not wrap to 0.

13:38 is not a number, either it is a string or it is a timevalue formatted as time or duration. You’ll have to be more specific there to describe your actual problem (“could not get it to even interpret” means what?). The underlying numeric timevalue would be 0.568055555555556, you can check by setting the number format to General on such time or duration (or hit Ctrl+M to clear all formatting). All date+time and duration values are represented in fraction of day floating point numbers, where 0.5 equals 12 hours.

[Lupp]

Congratulations. You clearly see the very important difference between TimeOfDay (TOD) and duration.
There is one fundamental problem caused by doubtable decisions about 4000 years ago, and never tidied up: The sexagesimal subdivision for hours and (later) minutes. Concerning the day the original half-day system with 12 parts also seems to be invincible.
Then there was added the bad habit to use the same subdivisions for durations. Since there isn’t a way currently to get a 60/60 subdivision, but to get it by using the d (day) as the unit of time, we generally do so concerning everyday subjects. Engineering and science mostly don’t, but use the s (second) or the a (year for long durations), and decimal orders of magnitude (“scientific notation”).
In spreadsheets durations are often (not always correctly) determined as differences of TOD values, which is an additional reason for the usage of the d as the unit of time.
Concerning the respective format for durations I would suggest to use [H]" h "MM" min " SS" s" or for lower resolution [H]" h "MM" min". The usage of explicit units disambiguates the format as one for durations from TOD formats.

Concerning the input: 13:38 should correctly be interpreted as equivalent to 13:38:00 or if sloppily used for a duration as 13 h 38 min. If you mean 13 min 38 s, you may enter 0:13:38 as a kind of shorthand.

Concerning the addition and probably wanted conversions see the attached example. The data are created there by random expressions using the non-volatile functions RAND.NV() and RANDBETWEEN.NV(). If your version of Calc doesn’t yet support them, change to the volatile standard functions.
ask318539durationFormat.ods

[keme]

Simple time input in Calc cells is the same for time-of-day and duration. Generally Hours:minutes:seconds.fraction (with some locales accepting different delimiters).

You can use a different format for displaying the time, e.g. H"h "MM"m "SS"s", but that is not an “input mask/template”. It will change the way the cell content is rendered after input, but not how the input is interpreted.

To force interpretation as minutes-separator-seconds, I believe the easiest would be to use text input and parse by formula, utilizing the TIME() function.

Also, to display accumulated minutes beyond 59 or hours beyond 23, you need to surround the relevant format section in square brackets.

Format code [MM]"m "SS.000"s" will display time data in minutes and seconds, to a thousandth of a second.

[ericjswrycan]

I had tried [MM] and it had not seemed to work. Trying a few things, I find that while it won’t accept [HH]:[MM]:SS, it will accept [MM]:SS (I suppose the [MM] does make the HH part nonsensical). Using [MM]:SS for all my cells, I still get a negative sum. I suppose I might need to take you advice about using TIME() but I’ve since found an awk script to solve my problem instead.

[keme]

… while it won’t accept [HH]:[MM]:SS, it will accept [MM]:SS …

Again, that format code does not make Calc interpret input as min:sec. The first segment will always be taken as hours.

Using [MM]:SS for all my cells, I still get a negative sum.

A negative sum indicates that some data are negative. If no “negative time” should be present, there seems to be an error in data acquisition. That may also affect the script you are using now. Make sure to check your results thoroughly.

[erAck]

that format code does not make Calc interpret input as min:sec

Since LibreOffice 6.4 it does, if a cell is formatted as MM:SS or [MM]:SS or MM:SS.00 or [MM]:SS.00.
See LibreOffice 6.4: Release Notes - The Document Foundation Wiki

[keme]

Thanks! I am sure I tested it first, but I may have stumbled into a different Calc at the time …

Someone have mercy on my posting above and put my erroneous claim inside strike tags. Editing own comments is forbidden so someone else needs to step up.

My apologies to the asker for my misleading info, and thanks again to @erAck. I learned something today.

As for the negative results:

Unlike MS Excel, Calc handles the concept of “negative time” just fine. This is often useful, but it defeats the “invalid data alert” which Excel would have displayed as a string of hash characters ########, and which might have helped for this particular case.

Not inherently wrong, in either case. Just a difference between the apps which is useful to be aware of.

[erAck]

For Excel it is “invalid data” because it does not handle it at all, it displays the same for (calculated) dates before 1900-01-01 (because it is a negative offset date+time value) that are perfectly valid dates for Calc. It’s not a data input help thing.

[keme]

It’s not a data input help thing.

Not as such, but it would have revealed any negatives present. Those will of course also be apparent in Calc when you have cells formatted for accumulated time. With plain MM:SS formatting, everything will look fine. Negative time appears as positive, because without the accumulation brackets in place we get time-of-day output. Time of day is never rendered as negative.

I did not intend to advise a transition to that other software. Only educating the user on typical behavior, also so that they may better understand and assess advice from other parties.

Clearly, Calc comes out as the better tool for working with time based data. It is still (perhaps even more) important to know how the tool works, and how it is different from the market leading tool (which is what “everyone else” will base their advice on).