Apache OpenOffice (AOO) Bugzilla – Issue 22345
Q-PCD Rework AutoFormatting behaviour in Calc for Number formats of Cells
Last modified: 2013-08-07 15:15:02 UTC
Q-PCD Rework AutoFormatting behaviour in Calc for Number formats of Cells (Competitive) user comment: The current behavior of automatically changing the cell format in Calc is different from other applications (I briefly looked into MS Excel XP, Lotus 1-2-3 9.7 and Corel Quattro Pro 2000). Simply said, no spreadsheet application handles this situation in an intuitive way. In all other apps, entering '14' in an 'HH:MM' formatted cell leads to the display '00:00' or '12:00 PM', often without any hint that the real value is Jan 13, 1900, ...! In my opinion this is even more unacceptable than our handling today. Second thought: I assessed the feature once again in Excel. Although this task is [was] related to entering time values, which I consider to be broken in Excel as well as in StarOffice (see above), it's subject really is about automatically changing the cell format. At a first glance, it seems that Excel changes the cell format automatically *only* if the cell was previously formatted as 'General' ('Standard' in German) *or* if the previos cell format has been assigned automatically. If the cell format was explicitely set by the user to a format different from 'General', it won't be changed automatically any more. As an exception to this, Excel also automatically changes the format if it was set by the user to #3 and #4 of the currency formats. I didn't detect any other situation were the cell format gets changed automatically. When entering a 'normal' number (an integer or a decimal) into an autoformatted cell that contains an 'advanced' format (like currency, time, percent, ...) Excel doesn't change the cell format back to 'General', but displays the number in the previously autodetected format. As soon as a different 'advanced' format is entered manually (e.g. '$4.00' in a cell that displays '10:15'), the autodetection assigns a format that matches the new input (in this case a currency format). Just as a minor observation: Excel distinguishes between the input of '3%' and '3.1%', such that the first is formatted without decimals (no matter what other percentage value later gets entered into this cell), and the second (and any other example with more than one decimal digit) is formatted with two decimals. Product concept: Calcs current behaviour for AutoFormatting the number format of a cell based on the input as well as its behaviour once a number format was chosen (manually the user or automatically by Calc) is inconsistent and confuses the user. Goal of this issue is to seek out the most sensible AutoFormattings as well as on initial data input in cells as well as the later handling on already formatted cells.
FT: First draft written and sent out for review. (http://specs.openoffice.org/calc/ease-of-use/enhance_number_recognition.sxw)
*** Issue 4352 has been marked as a duplicate of this issue. ***
FT->NN: The spec (see URL field) is finally approved by QA. Please also approve and start implementation. Thx.
FT: I forgot EM for string review in chain, sorry! Re-assigned to EM for string review first.
I never agreed to that. The second example in 6.3 still doesn't match the table. The second column of the table is completely wrong because input processing was mixed with the '%' sign that is added when you start typing. The rule to overwrite a date format with everything else leads to the original problem again (make a beautiful date format, accidentally type a single number, lose your format). Why not go with my original suggestion? Leave all parsing as-is, and apply the resulting format (more precisely: the default format for the resulting format type) only if: 1. a value was recognized, and 2. the recognized format type is incompatible with the cell format's type, and one or more of 3a. the old cell format was the default format of category "number", "date", "time" or "boolean", or 3b. the recognized format type was "boolean". That way, the automatically applied format from accidental date or number input will only ever overwrite an existing format that can be restored by just entering the right value. Condition 3a may be extended to include categories "percent", "scientific" or "fraction" (but not "currency"). Note that "General" is the default format for category "number". I don't agree with the details of the dialog change either, but maybe that's less important at the moment because it won't be changed for 2.0 anyway.
FT: Niklas updated the spec (esp. the matrix table). Please have a (final) look at it if you agree with the changes made (-> redlining!) If you agree with the spec content please forward this issue for implementation to Niklas (nn). If not back to me with justification. Thx a lot.
As discussed, changes accepted
Please also consider issue 16723. From issue 16723: I use the ISO standard date format YYYYMMDD (no separators). In a Date formatted cell this changes the format to Number. When forced back to Date (1999-12-31), it is displayed as -8794-03-22 in the cell and 03/22/-8794 in the bar. Win XP is set to yyyy-MM-dd (the closest it has). OOO should be able to accept this format. Example:July 11 2003 2:05 PM Eastern Standard Time = 200307111405-0500.
*** Issue 16723 has been marked as a duplicate of this issue. ***
*** Issue 25076 has been marked as a duplicate of this issue. ***
*** Issue 26296 has been marked as a duplicate of this issue. ***
Implemented in CWS "numinput". Changed: column3.cxx 1.9.284.1
Reassigning to QA for verification.
*** Issue 26941 has been marked as a duplicate of this issue. ***
Hi Frank, please take over
Hi Niklas, according to the Spec found on OOo this is not fixed for the Date column which can be found on Page 9 of the Specification. The Input always change to a date except for Boolean or text values. The attached file will show this behaviour. Frank
removed fixed
Created attachment 14080 [details] Testdoc for Numberrecognition
issue 26941: If I enter for example "1.875" into a cell, "1.88" is displayed. Excel displays "1.875", like I have entered it. Excel Standard-cell format shows up to 9 digits after the decimal point, which suit good to the default cell- width. Why don't you do so in Calc? For example in technical calculations, 2 digits after decimal point are not enough. In Calc I have to switch the number-format for many cells in order to have enough digits after decimal point. The way Excel handles this (up to 9 digits after decimal point) is much more userfriendly. Please fix this.
Frank, only the *default* date format is overwritten by non-date input. Column E in the attached document has a different format (4-digit year). This was the whole point of the issue, to not overwrite non-default formats.
reset to fixed
verified on cws numinput
*** Issue 27803 has been marked as a duplicate of this issue. ***
Found integrated on Solaris, Linux and Windows using src680m37
*** Issue 29217 has been marked as a duplicate of this issue. ***
*** Issue 30726 has been marked as a duplicate of this issue. ***
Just noticed some strange behaviour in OOo-calc. version 1.1.2, which I think relates to this issue. When I paste text directly from HTML-pages into calc. It ignores any formatting I have done in the spreadsheat - and as usuall, it tries to make wierd dates out of text-content. On the other hand, if I paste first in a text editor, and replaces any double spaces (double white space) with a single tab, and then copy-pastes the text into calc, it keeps all my formatting. Why is that?
*** Issue 33723 has been marked as a duplicate of this issue. ***
*** Issue 34853 has been marked as a duplicate of this issue. ***
*** Issue 36191 has been marked as a duplicate of this issue. ***
I am also having trouble: When I paste cells from Writer containing 1.1.3 or 4.18.2, Calc then converts these to dates when in fact they are paragraph numbers listed in a table. I assume you would have trouble copying OOo version numbers into a spreadsheet also :) For example: 1.0.1, 1.1.2, etc., which would be converted to dates.
Since Issue 33723 is supposed to be a duplicate of this Issue, I will work from this one. I am attaching sample Writer document that contains a table (slighly modified from an actual table that I am working on for my job) that needs to be copied exactly as is into Calc for manipulation. Notice that the first column contians paragraph numbers (4.12.1 for example), but not on every row, which precludes it being copied as unformatted text (which eliminates empty rows). If this table is copies and pasted as another format type (rtf, html), the first column's data is then converted to dates - even if the spreadsheet's cells were told to use the text data type before pasting the table.
Created attachment 21925 [details] sample writer document that contains a table with data that needs to be copied into calc