Bug 149139 - Calculation with time duration is not simple, it is in Mac Numbers
Summary: Calculation with time duration is not simple, it is in Mac Numbers
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
Depends on:
Blocks: Paste
  Show dependency treegraph
Reported: 2022-05-17 19:34 UTC by JohnTweed
Modified: 2023-08-18 17:00 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

With track time and extra columns showing hack required to get correct answer (11.43 KB, application/vnd.oasis.opendocument.spreadsheet)
2022-05-19 18:49 UTC, JohnTweed

Note You need to log in before you can comment on or make changes to this bug.
Description JohnTweed 2022-05-17 19:34:29 UTC
I've cut and paste some track times from MusicBrainz to get the following,
With SUM(A0:A7) I get this.... which is obviously not the answer anyone would expect.

Now the same in NUMBERS for Mac...
7m 23s
3m 50s
2m 28s
5m 21s
2m 46s
8m 5s
3m 7s
9m 56s
---> SUM(A0:A7) gives the correct answer, with no headache.
42m 56s

It shouldn't be difficult in LibreOffice,  there's a lot of people who want to make a simple duration calculation.  Without my Mac I would be going nuts trying to get LibreOffice to do the right thing.  I'd probably give up and use python/pandas

Steps to Reproduce:
1. write some durations into a column with [m]m:ss.sss  format
2. sum the column
3. stare in disbelief and the incomprehensible result.

Actual Results:
Shown above from Mac NUMBERS

Expected Results:
Shown above from Mac NUMBERS

Reproducible: Always

User Profile Reset: No

Additional Info:
Should have been more flexible in reading time durations
Comment 1 JohnTweed 2022-05-17 20:09:15 UTC
In NUMBERS, you will notice that the format is set to Minutes m Seconds s
If the total number of minutes exceed 60, then it will not wrap around to 0 (throwing away the hour part), but correctly continue to record minutes > 60.
Comment 2 raal 2022-05-18 20:47:35 UTC
Probably is not set cell format as date/time. Can you attach test file?
Comment 3 JohnTweed 2022-05-19 18:49:13 UTC
Created attachment 180233 [details]
With track time and extra columns showing hack required to get correct answer

Col A-D is the original paste, (after format switch to TIME).

The extra columns are my hack to see how it might be possible (if i didn't have an alternative, eg in Linux) still using Calc.
Comment 4 QA Administrators 2022-05-20 03:41:27 UTC Comment hidden (obsolete)
Comment 5 Roman Kuznetsov 2022-05-21 12:34:53 UTC
I copied your example here


and just pasted it into Calc

Calc asked me about format options, I checked option about date/time recognize and Calc inserted the time correctly

Then I wrote the sum formula below inserted data and set up [H]:MM:SS format for sum cell

Calc calculates the correct results

Version: / LibreOffice Community
Build ID: a2ffd71f20e14f797f30223fe29b3ac1e92eafdf
CPU threads: 4; OS: Linux 5.5; UI render: default; VCL: kf5 (cairo+xcb)
Locale: ru-RU (ru_RU.UTF-8); UI: ru-RU
Calc: threaded
Comment 6 LeroyG 2022-05-22 16:49:21 UTC
Excuse my ignorance: I have never used A0 as a reference.

If cells are formatted as M:SS, with SUM(A1:A8) I get 42:56.

You must look if the cell content is formatted as text (i.e., '7:23, or automatically aligns to the left, or with Ctrl+F8 the numbers remains black).

LibreOffice on Linux.
Comment 7 LeroyG 2022-05-22 16:52:31 UTC
Sorry! I send my comment before reading all posts.
Comment 8 JohnTweed 2022-05-24 09:05:52 UTC
Thanks to all for for taking time to look at this.
I think I see where the problem arose now.

Taking a random track list off Discogs, I copy/paste (MACOS) into calc.
If I paste with 'Paste -> +detect special numbers' then the time column values appear as 12:34:00 for the input '12:34',  changing format to TIME [HH]:MM:SS allows the (partially) correct sum(C0:Cxx).
The only problem is that the input MM:SS is interpreted as HH:MM:00,  but for my purpose it doesn't matter, it may for some.

Now .... 
If I do the paste with 'Paste Special -> +Unformatted text' then the time column comes out as text (but doesn't show as "'12:30" ) but after a format change to TIME [HH]:MM:SS. the column is now firmly TEXT and requires the extra steps to undo this and get back to a time value.  With only MM:SS this is especially difficult since the TEXT->TIME conversion does not recognise that text format as time, I have to force the TEXT to be '00:MM:SS' by prepending "00:" to the time strings in the column.

However, setting the option 'detect special numbers' does set the time column to be TIME type again.

So the problem is really now that 
a) Can't set the detect special numbers to see MM:SS not HH:MM:SS, but it doesn't necessarily prevent simple use for summing times.
b) The function TIMEVALUE(text) only seems to work for HH:MM:SS and is not flexible enough to see MM:SS or any other formatting.

So once my paste was converted to TEXT life became a bit more difficult.

In conclusion,
A misunderstanding of the necessary paste choice for the mixed text and time paste, meant that the time column was converted to text, making any further processing a lot more complicated (because of the limitations of the TIMEVALUE function).
Comment 9 JohnTweed 2022-05-24 09:37:05 UTC
I suppose the real difference between CALC and Mac's NUMBERS is how the change in formatting a column works.
When importing into CALC, if the column is made TEXT then no formatting will change that even if the TEXT is all numerical/time/date.

When importing into NUMBERS, it seems that the column remains TEXT until a format is changed.  The action of choosing a MM:SS format converts the text into the correct type, assuming that is a possible conversion.

So NUMBERS seems to post-process the pasted data, whereas CALC tries to pre-process the pasted data, but with a limited selection of conversion operations.
Comment 10 Michael Warner 2022-05-28 12:42:22 UTC
Based on Comment 8 and Comment 9 it sounds like this function has opportunities for enhancement, so I have updated the priority and status.
Comment 11 JohnTweed 2022-05-29 19:25:06 UTC
Thanks @Michael Warner.
I agree,  at the end of the day, we all want simplicity and it's nice when the software helps us along with that.  
Though I appreciate the difficulty of anticipating all the bizarre tricks that users may get up to that may break the 'nice' behaviour of any tool.