Description: I've cut and paste some track times from MusicBrainz to get the following, 7:23 3:50 2:28 5:21 2:46 8:05 3:07 9:56 With SUM(A0:A7) I get this.... which is obviously not the answer anyone would expect. 00:00.00 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
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.
Probably is not set cell format as date/time. Can you attach test file?
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.
[Automated Action] NeedInfo-To-Unconfirmed
I copied your example here 7:23 3:50 2:28 5:21 2:46 8:05 3:07 9:56 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: 7.4.0.0.alpha1+ / 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
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 7.2.3.2 on Linux.
Sorry! I send my comment before reading all posts.
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).
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.
Based on Comment 8 and Comment 9 it sounds like this function has opportunities for enhancement, so I have updated the priority and status.
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.