Download it now!
Bug 135249 - Allow duration inputs of 0:mm or 0:0:ss with values >59, or 0:mm:ss with mm>59
Summary: Allow duration inputs of 0:mm or 0:0:ss with values >59, or 0:mm:ss with mm>59
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.4.0.3 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard: target:7.1.0 target:7.0.1 target:6.4.7
Keywords:
Depends on:
Blocks:
 
Reported: 2020-07-28 23:59 UTC by gabrielhml
Modified: 2020-08-07 19:07 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description gabrielhml 2020-07-28 23:59:32 UTC
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).
Comment 1 Oliver Brinzing 2020-07-31 15:04:25 UTC
>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
Comment 2 Eike Rathke 2020-08-05 22:00:31 UTC
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.
Comment 3 gabrielhml 2020-08-05 22:17:20 UTC
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.
Comment 4 Eike Rathke 2020-08-05 23:04:58 UTC
(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.
Comment 5 Commit Notification 2020-08-06 01:19:28 UTC
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.
Comment 6 Commit Notification 2020-08-06 03:33:24 UTC
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.
Comment 7 Mike Kaganski 2020-08-06 07:32:47 UTC
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...").
Comment 8 Mike Kaganski 2020-08-06 07:40:56 UTC
... 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.
Comment 9 Commit Notification 2020-08-06 07:52:20 UTC
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.
Comment 10 Commit Notification 2020-08-07 19:07:48 UTC
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.