Created attachment 96151 [details]
snapshot format, cell, input box of Calc sheet
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.
Steps to reproduce:
enter 1:20 get 20:00
Operating System: Windows 7
Version: 184.108.40.206 release
reproducible with LO 220.127.116.11 (Win 8.1)
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
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.
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
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.
*** Bug 94771 has been marked as a duplicate of this bug. ***
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":
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:
Affected users are encouraged to test the fix and report feedback.
Works fine. Thanks Eike :-)
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.
(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
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.
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
Can't reproduce. Input of 2:30 for both formats results in 02:30, raw value 0.001736111111111
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 V18.104.22.168alpha1.
1. Sorry. In my comment #11 there was a typo. Correct version: V22.214.171.124alpha1.
Ah wait.. 126.96.36.199.alpha1 (cc57df8f942f239d29cb575ea5a7cb01405db787 of 2019-10-16) does not have this implementation (87660c3334c5150f20f3a7dbcd8f660922a203a5 of 2019-10-19), so no wonder.