In previous versions of Calc, if I inputted something like 0:65, it would automatically convert that to 1:05. That was very useful. This behaviour was changed, apparently according to this commit: https://gerrit.libreoffice.org/c/core/+/81087 However I want to suggest that this is a useful behaviour. What I suggest is that, if the Cell is formatted as duration or time, then this behavior shoud apply. If the Cell is not formatted as time, then this behavior should not apply. That way, if I want to input 200 minutes, for instance, I can have the cell automatically convert that to 3:20 (for example).
>This behaviour was changed, apparently according to this commit: >https://gerrit.libreoffice.org/c/core/+/81087 confirming, but obviously an intended change btw: with ms excel 2016: input 0:65 results in 0,0451388888888889 and can be formated to 01:05:00
There were earlier complaints that non-time sequences like 1:123 or 1:1:123 were accepted as times, so mileages may differ between users.. Not everything resembling a time should be an automatically valid time input, however, it might be a valid input if the first particles are 0: or 0:0: (or 00: or 0:00: or 00:00: for that matter) for those "number of minutes" or "number of seconds" inputs.
This is tricky It is also possible that the person writes 1:1:40 but does not mean time I agree with the suggestion that if the first particles are 0: or 00: then it should be understood as time, but more than that, it would be nice if as a general rule Calc would try to conform to the format of the cell. This would apply to other cases too. For example, when I have a date cell, if I input "1/5" it identifies a date. But if I input "1/2" it identifies as simply a "half". But the fact that I am writing "1/2" on a cell that was formated as a date should tell Calc that I actually mean a date. (maybe we could open a suggestion for this as well) In this case, if the cell is formatted as time, then it should override whatever other function and try to treat the cell as time. Calc doesn't have to try to decipher what the user is trying to input if the user has already stated what he is trying to input on the cell's formatting.
(In reply to gabrielhml from comment #3) > It is also possible that the person writes 1:1:40 but does not mean time Which can always be prefixed with an ' apostrophe to force text. > but more than that, it would be nice if as a general rule Calc would try to > conform to the format of the cell. This would apply to other cases too. That quickly leads into a hell of confusion, the more if empty cells are formatted already. Number display formats are not input masks. > For example, when I have a date cell, if I input "1/5" it identifies a date. > But if I input "1/2" it identifies as simply a "half". Only if AutoCorrection is enabled to change 1/2 to ½ which you can revert with Ctrl+Z. > In this case, if the cell is formatted as time, then it should override > whatever other function and try to treat the cell as time. An input of 1:123 quite certainly is not meant as time or duration.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/1616b53292cdc22c04d07bb21e71bf43dcd22299 Resolves: tdf#135249 Duration input 0:123 or 0:0:123 or 0:123:59 is valid It will be available in 7.1.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-0": https://git.libreoffice.org/core/commit/bd33e2673b3d97a6f1d7ab16eb8f83faae09d980 Resolves: tdf#135249 Duration input 0:123 or 0:0:123 or 0:123:59 is valid It will be available in 7.0.1. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Thanks Eike! A nice change. (In reply to Eike Rathke from comment #4) > (In reply to gabrielhml from comment #3) > > It is also possible that the person writes 1:1:40 but does not mean time > Which can always be prefixed with an ' apostrophe to force text. The same could be told about 1:1:123 ;-) > > but more than that, it would be nice if as a general rule Calc would try to > > conform to the format of the cell. This would apply to other cases too. > That quickly leads into a hell of confusion, the more if empty cells are > formatted already. Number display formats are not input masks. Both PoVs have pros and contras. I don't think that the "hell" would be larger than now, and that comes not from some software problems, but from spreadsheet concept (and tasks it is designed for) complexity, and general human laziness. However, an increase of consistency would be a good thing. We already take formatting into account when treating user input: first when a field is formatted as text; then IIRC times vs durations started to be considered recently ... and that goes against the "Number display formats are not input masks" idea. Personally I support the suggestion to extend consideration of cell formatting when interpreting cell input. Like "first try to treat cell format as kind of input mask; then consider general group of cell format; only then fall back to normal processing". So a format like "DD/MM/YY" would result in considering an input first as DMY; then as date (using configured acceptance patterns); then generic processing (checking numbers, percents, currencies, times, formulas etc...). That would possibly be a major change, and could benefit from having a configuration option, but that seems increasing consistency over current situation (when we declare "formats are not masks, except here, and also here, and again here...").
... at least extending scope of formats to serve as masks, although complex internally (since formats of course are more complex than masks would be, which opens for ambiguity), seems simpler from user PoV, than introducing another cell format category like "input mask", so that users need to handle three things (actual stored data type; display format; input mask) vs existing two (actual stored data type; display format). Additionally, IMO input masks would be largely redundant, almost always being a kind of simplified display formats.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-4": https://git.libreoffice.org/core/commit/3115faaa72b25bdd3af140d275635b0f0cd06e3a Resolves: tdf#135249 Duration input 0:123 or 0:0:123 or 0:123:59 is valid It will be available in 6.4.7. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/b02d05f460fb43424d4288969ea826afc06b66e5 tdf#135249: sc_ucalc: Add unittest It will be available in 7.1.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.