I'm trying to build a spreadsheet with a column of MM:SS formatting. However I get impossible to understand results. I enter 30:08 and get 6:06 displayed. The application is to list some video files with their lengths from 00:00 to 59:59 seconds. and then do time sum operations on the lengths with =sum(...) and so forth. However formatting an entire column as MM:SS does not appear to work correctly. Things appear to work for some values, but for other values, the results are totally nonsensical. It appears that the problem is that if you take the default formatting of HH:MM rather than resetting it manually to MM:SS, then you get values that cannot be converted to MM:SS. For example, if I have mistakenly formatted the column to 13:37 option, this is actually HH:MM. Which is probably what gave me the strange result of 6:06 when I entered an hour larger than 23. But I can see no way to correct my error without entering all the data again. Formatting to TEXT does not do that. It seems reasonable to me that I should be able to format to text, then back to MM:SS and correct most of the values. At that point I should only have to re-enter the values that fall outside the range of 23:59. But this apparently does not work. Formatting to text does not remove the "Time Valueness" of the value and reduce it to text again apparently. Whatever is going on is completely opaque without a deep understanding and a spreadsheet should not behave that way in simple cases, IMHO. It seems to me that doing this job should be so simple... But apparently the way that time formatting works, it becomes so very hard. One apparently has to have a deep understanding of how the spreadsheet values work, and how formatting works BEFORE HAND or you will enter a bunch of data and then find you have to enter it all again because you cannot easily just reformat it to work. I look forward to anyone's reaction to this observation. Thanks a bunch, WW
OMG. It's even worse than I suspected. I format a column to MM:SS and then type 11:19 and get 19:00. So there is no way to type in the minutes and seconds of the duration of a video? Entry of time always assumes an absolute time rather than a duration? and there is no way to say that the FORMAT is a duration rather than an absolute time?
Created attachment 119293 [details] Entry of 11:19 is taken as a Time rather than a duration
Created attachment 119294 [details] User formatting spec of MM:SS
Ok. I have done a deep dive into how calc does time. Let me summarize in case I don't have it down to the quantum level: (1) Time is represented as a real number of days. Cool. I get that. (2) Format shows that value. No such thing as Duration. That's a problem for interpretation by the user, and not the software apparently. (3) [m]:s or some such formatting allows representing a large number of minutes and the residual seconds. Which is a hack that lets the software ignore that it does not understand duration vs absolute time. So my remaining confusion is "Why doesn't the format affect the input method?". Apparently when you type a time into a cell, the format is completely ignored. So if you type: 11:19 it is taken as 11:19 AM, even when it is typed into a cell formatted as [m]:s, which seems like complete nonsense to me. I didn't test the case of 111:19, which should make perfect sense based on the format, but probably causes something very strange since if you don't take format into account, 111 is an invalid value for any time field except "days". See I grew up programming Freaking FORTRAN where there were INPUT THE FREAK FORMATS. Yes boys and girls, I am that freaking OLD. So why doesn't the format condition the interpretation of the text as it is typed into a Cell?? if that were the case and I typed 11:19, or 111:19 into a cell formatted as [m]:s then it would make sense and you would get what you expected. After all that's why you formatted the cell in the first place? Well if there is a "Backwards compatibility problem" with doing this, for some reason that I can't fathom at this time, then add a check box to the Formatting dialogue that says "[] Format affects input" or some such intelligent name. As far as I can tell this has no bearing on the format of the spreadsheet file, except that the format spec needs to be saved with this new BOOL setting. But the file format is XML - eXtensible Markup Language? Correct? So no problem adding a new setting, right? Comments on this bug fix? - ww
Created attachment 119299 [details] format [HH]:MM Try to use format of cell: [HH]:MM
NEEDINFO while waiting for response to comment 5.
First, for duration use [HH]:MM:SS or [MM]:SS format. Second, it's a display format, not an input mask, time input is accepted as h:m or h:m:s Third, there's already an enhancement request. *** This bug has been marked as a duplicate of bug 76441 ***