| Summary: | Calculation with time duration is not simple, it is in Mac Numbers | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | JohnTweed <dump4crap> |
| Component: | Calc | Assignee: | Not Assigned <libreoffice-bugs> |
| Status: | NEW --- | ||
| Severity: | enhancement | CC: | 79045_79045, buzea.bogdan, raal |
| Priority: | medium | ||
| Version: | 7.3.3.2 release | ||
| Hardware: | Other | ||
| OS: | All | ||
| Whiteboard: | |||
| Crash report or crash signature: | Regression By: | ||
| Bug Depends on: | |||
| Bug Blocks: | 102593 | ||
| Attachments: | With track time and extra columns showing hack required to get correct answer | ||
|
Description
JohnTweed
2022-05-17 19:34:29 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. 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. |