Bug 48758 - TABLE: Number Format Recognition is performed though Option "Number Format Recognition" is not Marked
Summary: TABLE: Number Format Recognition is performed though Option "Number Format Re...
Status: RESOLVED WONTFIX
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Writer (show other bugs)
Version:
(earliest affected)
3.3.4 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks: Writer-Tables-Number-Recognition
  Show dependency treegraph
 
Reported: 2012-04-16 01:45 UTC by Harald Koester
Modified: 2017-11-03 13:54 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Examples of recognized numbers (13.33 KB, application/vnd.oasis.opendocument.text)
2012-12-02 17:13 UTC, Harald Koester
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Harald Koester 2012-04-16 01:45:33 UTC
Problem description: 

(1) Start LO and open a new text document.
(2) Mark Option "LibreOffice Writer > Table > Input in tables > Number recognition", if not marked already.
(3) In the same Dialog reset options "Number format recognition" and "Alignment" if not reset already.
(4) Insert a table.
(5) Mark all cells of the table and assign number format (Menu: Table > Number Format...): Category: "Number"; Format: "-1,234.12"; Language: "English (USA)"
(6) Insert "123.4567" into a cell. Go to next cell (with mouse or with button Tab). The number changes to "123.46". The number format of the cell is still "Number".

The help text of option "Number format recognition" says: "If Number format recognition is not marked, only input in the format that has been set at the cell is accepted. Any other input resets the format to Text."

Hence I expect this: Change of the number format to "Text" and the contents should be still "123.4567".

Problem also observed with the number formats Percent, Date and Time. I did not check the other number formats, but I assume that the problem also exist there.

Behaviour observed in Version 3.4.6 and 3.5.2.              

Browser: Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.9.2.28) Gecko/20120306 Firefox/3.6.28
Comment 1 sasha.libreoffice 2012-04-16 07:48:12 UTC
Reproduced also in 3.3.4 version on Fedora 64 bit
As demonstrated in step 6 , some digit thrown away. The same situation if we copy-paste number from text and then press Tab.
If copy-paste part of another table, number retains as is.
So, for Writer in this case corresponding to selected number format is more important than retain all typed digits. Expected that format will changed to "Text" for prevent data loss.  

If we enable option "Number format recognition" (mentioned in step 3) then situation for number 12,3456789 is not better. It recognizes as "Number Standard" and changes to 12,35. In comparison in Calc: number 12,3456789 recognized also as "Number Standard", but no loss of digit there.
So, "Number Standard" in Writer is wrong, forces data loss.
But it may be another bug or documented feature.

Changing version to 3.3.4 as most early reproducible
Comment 2 Rainer Bielefeld Retired 2012-05-02 23:11:58 UTC
I believe this Bug is invalid. I do not expect that due to original report I will have to input "000,001.00" instead of "1" for a number recognition and that "12,000,000" will not be recognized.

Help test only wants to say: With reporter's setting only type of number that is defined for cell will be recognized. That means in a currency cell no date will be recognized. And that#s exactlywhat I observe with 3.5.2

Without new arguments I will close this one as NOTABUG until 2012-05-13
Comment 3 sasha.libreoffice 2012-05-03 01:33:02 UTC
May be "Number Standard" problem from comment 2 split to another bugreport?
Comment 4 Harald Koester 2012-05-11 10:37:29 UTC
(In reply to comment #2)
> I believe this Bug is invalid. I do not expect that due to original report I
> will have to input "000,001.00" instead of "1" for a number recognition and
> that "12,000,000" will not be recognized.
> 
> Help test only wants to say: With reporter's setting only type of number that
> is defined for cell will be recognized. That means in a currency cell no date
> will be recognized. And that#s exactlywhat I observe with 3.5.2
> 
> Without new arguments I will close this one as NOTABUG until 2012-05-13

Hello Rainer,

I think you are right, it is not a bug. To my opinion the help text is not very specific and irritated me and so I wrote the bug report. 

In order to write a better text, I have tried several cases and found out that the function is a bit complicated. The result is this text:

**********
*Number recognition*

Specifies that the number inserted by the user in a text table is recognized, if the number format category is a number category (number, percent, currency,...). The number is formatted according to the format specified in the dialog Number Format. More details see below at option “Number format recognition”.

If Number recognition is not marked, inserted numbers (also dates, time,...) are always saved in text format even a number format has been assigned before to the cell.


*Number format recognition*

Has no effect if Number recognition is not marked.

If “Number format recognition” is not marked:

[1] If the number format category assigned to a cell is Percent, Currency, Date, Time, Scientific, Fraction or Boolean Value and the input is recognized as a valid value (with according unit) of the according category (e. g. 12% in category Percent or 2012-01-01 in category Date), the input is formatted according the format assigned in the Number Format dialog.

[2] Furthermore if the number format category assigned to a cell is Number, Percent, Currency, Date, Time, Scientific, Fraction or Boolean Value and the input is recognized as a number value without a unit (Scientific and fraction values are not recognized as numbers.), the number is formatted and in some times converted (e. g. number to date) according category and format assigned in the Number Format dialog. 

[3] In all other cases the cell category changes to Text and the input is not changed.

If “Number format recognition” is marked:

In addition to [1] and [2] number formats categories different from the assigned category are recognized (not category “Number”, is already recognized in [2]). The number category of the cell changes to the recognized and the number is formatted with a default format of the specific category.
***************

I am not sure, if my description is completely correct, hence the number of test cases is enormous especially if you consider all language specific number formats. But I think it's anyway better than the existing help text. So I propose to exchange the help text with the text above or in the sense of this text.

By the way: I think the display of the number format is not correct if more than one cell is marked and if the number format category of this cells is different:
(1) Create table. (2) Assign category Percent to first cell. (3) Assign category Currency to next cell. (4) Mark both cells. (5) Display dialog “Number Format”. Now Category is “Number” and Format is “Standard”. I expect a display, that indicates, that the format is different. (e. g. for Category “different categories” and for Format “different formats”). Bug? New bug report?

And another point: I miss the date-time-format “YYYY-MM-DDThh:mm:ss” (Example: 2012-01-01T12:00:00) of the ISO 8601 as a proposal in the Number Format dialog. Bug report with enhancement?
Comment 5 sasha.libreoffice 2012-05-11 23:40:27 UTC
> for Category “different
> categories” and for Format “different formats”). Bug? New bug report?
Something like this exist, but for Calc: Bug 42989

ISO 8601 mentioned here: Bug 41044
Comment 6 Harald Koester 2012-05-25 02:15:11 UTC
Bug 42989 is identical to this display problem. I added a comment to bug 42989, that the problem also exists in Writer tables.

The mentioned date problem in connection with the ISO 8601 needs some more research particulary with the other OpenOffice.org-bugs mentioned in bug 41044. Perhaps I will pick it up again later or someone other will do it.
Comment 7 Rainer Bielefeld Retired 2012-06-07 23:02:38 UTC
Can someone create a brief instruction what has to be done for a fix?
Comment 8 Harald Koester 2012-06-25 05:56:44 UTC
(1) Check if my descriptions of options "Number Recognition" and "Number Format Recognition" in comment 4 are correct. If necessary correct descriptions.

(2) Change the help text of both options.

(3) Add the date-time-format “YYYY-MM-DDThh:mm:ss” as a predefined number format to LO.

Other problems mentioned here should be treated in bug 42989 and bug 41044.
Comment 9 Harald Koester 2012-12-02 17:13:46 UTC
Created attachment 70935 [details]
Examples of recognized numbers
Comment 10 Harald Koester 2012-12-02 17:15:27 UTC
I had look to this bug report again and I also tried some more things. The result is “hopefully” an improved description of the number recognition with examples and two additional remarks. To my opinion this description could replace the help text of the options “Number Recognition” and “Number format Recognition”.

< Beginning of description >

_Number recognition_

Determines that the number recognition in tables is active. A number category is assigned to each cell. Generally the number recognition only works in cells with number categories Percent, Currency, Date, Time, Scientific, Fraction and Boolean Value. The number recognition works as follows: 

(1) A number is always recognized, if the input is recognized as a valid number value with a unit according to the number category of the cell. The cell is formatted according the format assigned in the Number Format dialog. 
Examples: An input of “12 %” in a cell with category Percent changes to “12.00%” or an input “10/05/20” in category Date changes to “2010-05-20”.

(2) Furthermore a number is always recognized, if the input is a valid number value without a unit. The cell is formatted according the category and the format assigned in the Number Format dialog. (Hint: Value is converted, if category is Date or Time.)
Examples: An input of “12.345” in a cell with category Percent changes to “12.35%”, an input of “1234.56” in category Currency changes to “$1,234.56” or an input “23456.78” in category Date or Time changes to “03/20/1964 18:43:12”.

(3) If the option “Number format recognition” is marked, an inputted number with a unit is also recognized even the unit is different to the number format of the cell. The unit has to in accordance with a unit of one of the categories. The category changes according to the inputted unit. The cell is formatted with a default format of the according category.
Example: An Input of “12.345%” in a cell with the category Currency changes to category Percent and “12.35%” is displayed.

(4) With all other inputs the number category changes to “Text” and the inputted text is not changed.

If “Number recognition” is not active, all inputted numbers are always saved in text format.

< End of description >

Furthermore I found a difference of the number recognition in the category Percent between English and German. I will add an attachment where differences are marked in red. I think it's not really a bug but I do not believe, that's intended.
Hint: In English a dot is used as decimal mark and a comma is used as thousands separator. In German it 's exactly the other way round. 

Another interesting thing: If you type in a “1” in a cell with the category Date the content changes to “1899-12-31”. First I thought, that's strange, why does it not begin with 1900-01-01? Here is the story to this point:

http://www.joelonsoftware.com/items/2006/06/16.html
Comment 11 retired 2013-11-23 17:42:29 UTC
Is this bug still valid / reproducible with the latest LO release?  Currently 4.1.3.2: http://www.libreoffice.org/download/

Setting to NEEDINFO until more detail is provided.

After providing the requested info, please reset this bug to UNCONFIRMED. Thanks :)
Comment 12 Cor Nouws 2013-11-23 21:51:31 UTC
(In reply to comment #11)
> Is this bug still valid / reproducible with the latest LO release? 
> Currently 4.1.3.2: http://www.libreoffice.org/download/


Apart from that - with instructions in the initial report - the setting of the checks in the dialog seem to work opposite.... IMO this issue provides an useful improvement for the Help .. ?
Comment 13 Harald Koester 2013-11-26 14:39:30 UTC
Due to the different problems and several comments of this report, it has become a little confusing. In order to improve this I wrote three new reports (bug 72038, bug 72039, bug 72040), which describe the different problems. So to my opinion this report (48758) can be closed.
Comment 14 Robinson Tryon (qubit) 2013-12-24 02:06:22 UTC
(In reply to comment #13)
> Due to the different problems and several comments of this report, it has
> become a little confusing. In order to improve this I wrote three new
> reports (bug 72038, bug 72039, bug 72040), which describe the different
> problems. So to my opinion this report (48758) can be closed.

Per Harald's comment:
Status: RESOLVED WONTFIX