Bug 76441 - FORMATTING: MM:SS time input if formatted as such
Summary: FORMATTING: MM:SS time input if formatted as such
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
URL:
Whiteboard: BSA target:6.4.0 target:7.2.0
Keywords:
: 94771 (view as bug list)
Depends on:
Blocks: Number-Format Calc-Enhancements
  Show dependency treegraph
 
Reported: 2014-03-21 10:47 UTC by Lu
Modified: 2021-05-21 07:47 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
snapshot format, cell, input box of Calc sheet (67.96 KB, image/jpeg)
2014-03-21 10:47 UTC, Lu
Details
Example for what Wolfgang Jäger claimed before setting th bug REOPENED (120.96 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-11-05 14:33 UTC, Wolfgang Jäger
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Lu 2014-03-21 10:47:55 UTC
Created attachment 96151 [details]
snapshot format, cell, input box of Calc sheet

Cell format
MM:SS
type in 1:20 or 01:20

20:00 appears in cell and 01:20:00 AM appears in input box

tried various combos of reformating, retyping, editing contents, no luck.  Just checked - typing in 00:01:20 works, apparently format defaults to HH:MM:SS

This has been a bugbear for me with various spreadsheets [XL etc] for 30 years.  People can't conceive of day/time independant of calendars.

Problem description: 

Steps to reproduce:
1. ....
2. ....
3. ....

Current behavior:
enter 1:20 get 20:00

Expected behavior:
1:20
              
Operating System: Windows 7
Version: 4.1.5.3 release
Comment 1 A (Andy) 2014-03-21 21:36:47 UTC
reproducible with LO 4.2.2.1 (Win 8.1)

Steps Done:
1. Open CALC
2. Select the cells A1 and A2
3. Go to FORMAT -> CELLS -> tab NUMBERS
4. Go to TIME in CATEGORY and select 13:37:46 in FORMAT
5. Go to the field FORMAT CODE and delete "HH:" (now there is only MM:SS left) and press the OK check on the right of the entry field and press afterwards OK to close the dialog box -> a user-defined time format is added
6. Select the cell A3
7. Go to FORMAT -> CELLS -> tab NUMBERS 
8. Go to TIME in CATEGORY and select 37:46,00 in FORMAT
9. Go to cell A1 and type 01:20
10. Go to cell A2 and type 00:01:20
11. Go to cell A3 and type 01:20

Results:
A1 gets 20:00
A2 gets 01:20
A3 gets 20:00

If a cell has the format MM:SS and I type there 01:20 then I would expect as output 01:20.
Comment 2 Jean-Baptiste Faure 2014-03-22 11:10:35 UTC
For me it is not a bug. Time format is not an input format, it is a viewing format.

Hi Eike: do you agree?

Best regards. JBF
Comment 3 Eike Rathke 2014-03-25 14:48:02 UTC
Agreed, cell number formats are display formats, not input masks. Time input is accepted as h:m or h:m:s

However, one could argue that if a cell is already formatted to MM:SS a digit:digit input could be accepted as m:s as a special case.

Changing this to RFE.
Comment 4 Eike Rathke 2016-09-03 09:22:32 UTC
*** Bug 94771 has been marked as a duplicate of this bug. ***
Comment 5 Commit Notification 2019-10-19 09:04:06 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/87660c3334c5150f20f3a7dbcd8f660922a203a5

Resolves: tdf#76441 accept two digit groups MM:SS input if formatted as such

It will be available in 6.4.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 Laurent Balland 2019-10-19 09:11:49 UTC
Works fine. Thanks Eike :-)
Comment 7 Wolfgang Jäger 2019-10-24 20:16:28 UTC
Clearly a deterioration, imo.
I would judge the new behavioiur a bug, not a feature. Formatting durations with Time-Of-Day means should be discouraged anyway. It is in conflict with ISO 8601 and there are clearly better alternatives.

Date acceptance patterns are independent of the 'Numbers' formats of the cells. They might better be assignable to the cells instead of having to set them for the application. Anyway it's not a good idea to use very different and incompatible strategies for very similar situations. Why not create time acceptance patterns?

Develop acceptance (recognition) patterns vor every kind of specialised numeric data. Make them applicable to cells also via cell styles.
Comment 8 Eike Rathke 2019-10-24 22:56:36 UTC
(In reply to Wolfgang Jäger from comment #7)
> Clearly a deterioration, imo.
> I would judge the new behavioiur a bug, not a feature. Formatting durations
> with Time-Of-Day means should be discouraged anyway. It is in conflict with
> ISO 8601 and there are clearly better alternatives.
What? Apart from that, people are using duration formats and [MM]:SS always was a valid duration format.

> Date acceptance patterns are independent of the 'Numbers' formats of the
> cells.

This is not about date acceptance patterns.

> They might better be assignable to the cells instead of having to set
> them for the application.

You also want to be able to input a date in an unformatted or differently formatted cell.

> Anyway it's not a good idea to use very different
> and incompatible strategies for very similar situations. Why not create time
> acceptance patterns?

There is a time acceptance pattern, that of your locale, digit groups separated by the time separator. Which usually is hh:mm or hh:mm:ss or 00:mm:ss but in this specific case if already formatted as MM:SS also accepted as mm:ss instead of hh:mm.

> Develop acceptance (recognition) patterns vor every kind of specialised
> numeric data. Make them applicable to cells also via cell styles.

Input masks on spreadsheet cells? Good luck with that.
Comment 9 Wolfgang Jäger 2019-11-05 09:21:14 UTC
Calc V6.4.0.0alpha1
UI=English(UK)
locale=English(UK)
Cell format 'Numbers' language = Default - English(UK)
Cell 'Numbers' format code first  example = MM:SS
Cell 'Numbers' format code second example = [MM]:SS
Input = 2:30
Diplay after ENTER first  example:  30:00
Diplay after ENTER second example: 150:00

Asking the cell for its value and formatting that value a ordinary number results in 0.104 ... which is correct for 2 h 30 min.

Setting to REOPENED
Comment 10 Eike Rathke 2019-11-05 12:42:09 UTC
Can't reproduce. Input of 2:30 for both formats results in 02:30, raw value 0.001736111111111
Comment 11 Wolfgang Jäger 2019-11-05 14:33:30 UTC
Created attachment 155534 [details]
Example for what Wolfgang Jäger claimed before setting th bug REOPENED

I attach a simple example for what I claimed, made with V3.4.0.0alpha1.
Comment 12 Wolfgang Jäger 2019-11-05 14:37:23 UTC
1. Sorry. In my comment #11 there was a typo. Correct version: V6.4.0.0alpha1.
Comment 13 Eike Rathke 2019-11-05 18:10:33 UTC
Ah wait.. 6.4.0.0.alpha1 (cc57df8f942f239d29cb575ea5a7cb01405db787 of 2019-10-16) does not have this implementation (87660c3334c5150f20f3a7dbcd8f660922a203a5 of 2019-10-19), so no wonder.
Comment 14 Commit Notification 2021-05-21 07:47:19 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/2d8f77408ad3b7918096ad2818db266074c66061

tdf#76441: sc_ucalc: Add unittest

It will be available in 7.2.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.