Apache OpenOffice (AOO) Bugzilla – Issue 33723
NF-DATE: Ability to turn off Number Recognition for incomplete dates in Calc
Last modified: 2015-02-20 20:46:31 UTC
This is a request to turn off Number Recognition in Calc; the same that is available in Writer. Example: 1-17 (one through 17, a generic number range) turns into 1/17/04 (a date)
Hi, turning off number recognition for a spreadsheet would be contra productive. Instead we've changed the autoformatting process. Have a look at Issue 22345 which I've set this one double too. Frank *** This issue has been marked as a duplicate of 22345 ***
closed double
Number Recognition in Calc still has problems that issue 22345 did not solve. When pasting a table from Writer into Calc that contains cells with paragraph numbers such as 1.1.4 or 4.17.1, these are then converted into dates. I think we should take another look at having a way to turn off number recognition in the Calc preferences.
Sorry, but all is said in the spec for Issue 22345 . Pasting with CTRL+v just pastes the original format too overwriting the preset one. Using unformated text will keep the number formats. Frank *** This issue has been marked as a duplicate of 22345 ***
closed again as double
Sorry about this Frank, but since I cant' reopen Issue 22345. I'll reopen this one :) In Issue 22345 I have attached a sample Writer document with a table (modified from one at my work), that needs to be copied "as is" into Calc. Copying unformated text will keep the number formats, but not the table rows or columns, which is also critical, as you will see when you try copying the first column only and pasting that into Calc as unformatted text.
Hey, no problem, good arguments should be heard and considered. @falko please comment on this Issue and the use case given in Issue 22345 . Frank
Sounds good to me. Here is the description: 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 21963 [details] sample writer document that contains a table with data that needs to be copied into calc
I think someone else mentioned that in Excel, if you click undo, it will undo the autoformat leaving the original data that was pasted.
FT: Re-assigned to requirement default user
*** Issue 72449 has been marked as a duplicate of this issue. ***
This issue has a lot of resemblance to issue 87999. see http://www.openoffice.org/issues/show_bug.cgi?id=87999 That issue does have 11 votes, too.
This issue is important and listed on the quarterly review for Calc: http://wiki.services.openoffice.org/wiki/2008_Q2_Review_of_Spreadsheet_Project Therefore adjusting target to 3.x.
Reading the example (aaronforjesus Fri Jan 28 2005) and thinking about the problems I know/read about, I think that the summary of this issue should read "Ability to turn off Number Recognition for Date and Time in Calc" What do others think?
You don't want to turn off number or time recognition, but incomplete dates. Adapted summary.
To me, the issue seems to be best described as turning off date/time recognition, at least for dates and/or times that are not properly formatted. Maybe the best way to do this is add a more detailed date/time recognition format, where all permissable formats are enumerated in detail. (Something like for normal number formats, but separately.) And adding the option of permissive date format, and maybe permissive time format, where alternate separators could be accepted. e.g. for date one could have : yy/mm/dd mm/dd yy-mm-dd yyyy/mm/dd would only accept as dates strings such as 09/02/22 (as 2009/02/22) or 09/21 (as 2009/09/21) or 08-11-23 (as 2008/11/23) or 2007/05/25, but not 2007-05-25 unless the permissive flag is set (wrong separator) (These examples all being in order year-month-day. Other orders could be used.) Similarly for time. An option to use system defaults would be useful, with the same option for permissive separators. Thus for those who wish to avoid auto-format of dates, one could select system defaults in non-permissive mode, or even no formats (no system defaults and empty list of formats) (My personal option would be probably be the last.)
This is ridiculous. I just wasted 45 minutes of my time trying to open a single file with semicolon-separated values in Calc only to find out that it is impossible. Well, it is technically possible, but the data are useless. Trying to change the cell's data format from "date" to standard numeral is hopeless either, because the value itself is changed, not just its representation. Here is the file, for your own amusement: http://fijam.eu.org/other/jcr_recs.txt I can't see how this could have got past any Q&A team. I ended up writing a python script to parse my file into something useful. Productivity, right.
fijam: The file you provided does not contain any dates. It imports properly in OOO2.4.1 and OOO300m9 on Vista. Need better information: OS, OOO version, and file that does not import properly. TomW
Created attachment 59461 [details] screenshot - conversion to dates
Indeed, it does not. I attached a screenshot to better illustrate the problem. Windows XP SP3, OOO300m9 build:9358
fijam: After I found a "locale" in my language settings that would replicate the issue shown in your jpg, I found that I could import the file correctly by changing the field type in the import dialog to "US English". As noted before, My US English system imported the file with out issue. Whether your systems language includes the "US English" in the Import Dialog is unknown to me. TomW
I tried the same file and the behaviour of my version of open office (OOO 300m9 build 9358) is still very surprising! I have a Dutch localized system, have unchecked the "same as local settings (,)" check box for the decimal separator key (tried also with checking this box, but it makes no difference whatsoever), have no intention to inform the system that I am using anything else than the Default Locale (this is something I can't expect my colleagues to buy!) and my locale dictates this: number: 123 456 789.00 (space separates thousands, dot is decimal separator key) short date notation: 2009-01-19 (dash as date separator key) all right then, the line to be imported is ACCOUNTS CHEM RES;0001-4842;24129;16.214;1.736;148;7.3; in the text import preview this shows as ACCOUNTS CHEM RES | 0001-4842 | 24.129 | 16.214 | 1.736 | 148 | 7.3 when imported it actually gives ACCOUNTS CHEM RES | 0001-4842 | 24129 | 16214 | 1736 | 148 | 2009-03-07 why does that dot in '7.3' get interpreted as a date separator? why does that dot in 24.129 show in the import preview? is the import preview using '.' as thousands separator? how's that? why do the decimal dots in 16.214 and 1.736 disappear in the imported data? please note again that in my system locale I have a space for thousands separator, a dot for decimal separator and a dash for date separator!
Thank you for your prompt responses and guiding me in the right direction, Tom. Choosing "Western Europe(ASCII/US)" (I did not find US English there) in the import dialog did the trick. I should've probably stated my locale in the first place but I thought it was not relevant. Apparently, the Number Recognition works differently with different decimal separators - the default one for my locale is "," while the problematic file used ".". However, I believe the option to disable the Number recognition is desirable in the long run since it confuses the average user (as depicted).
Created attachment 59474 [details] Screen Shot of Calc Import Dialog
fijam: I have attached a screen shot of the import dialog showing that the selection of the "US English" is made after selecting a field column in the window. This enables the "Field Type" Dropdown box where you can make the Selection for "US English". I should have been more explicit in my previous post. TomW
Problems are not only with date - Calc should also have "Ability to turn off Number Recognition for Currency and Scientific numbers in Calc". Lots of Lithuanian OOo users don't understand why they can enter their postal code with OpenOffice Calc - for examply my postal code is LT-3000, but Calc automatically changes this to Lithuanian currency (Lt) -3000,00 Lt Users even can't copy-paste text from OOo Writer's table :( Same problem is when entering E letter between numbers - Calc automatically changes this to Scientific numbers :( It's very important for Lithuanians to have user friendly ability to turn off such autoreplacing: lt3000 is replacecd to 3000 Lt LT-12012 to -12.012,00 Lt 120E12 to 1,20e+014 9E9 to 9,00e+009 Lots of users don't understand why there are no way to turn off this unintuitive Calc feature and are starting to user other Spreadsheet software, like Gnumeric, see discussion at Lithuanian support mailing list - https://lists.akl.lt/pipermail/naudotojai/2009-April/001776.html Please tell me, if I should report "Ability to turn off Number Recognition for Currency and Scientific numbers in Calc" as another bug or just change summary of this bugreport?
@egle: Your problem is a different one. The examples you have given are all texts and the user can avoid problems by formatting the cells to text-format before entering the texts or by using a prefix apostrophe to determine it as text. The issue here is about handling of numbers, whether they are recognized as date and time or not.
@regina with all due respect, the essence of the issue is automatic special formatting (be it dates, currency or whatever) interfering with a normal entry of data. There may be some users that prefer this behavior, but it should be optional, and preferably off by default. By the way, often ordinary numbers or texte + numbers is reformatted as dates when it doesn't even match any of the registered date formats. I've also encountered entries being unexpectedly converted to currency. (My locale is fr-CA.) e.g., if the calc date format is yyyy-mm-dd (using - as separator), entering 1.1.16 will be incorrectly formatted as the date 2001-01-16, even though it does not have the correct separators. Then changing the format to texte will result in some bizarre number, totally unrelated to what was entered. If someone wants a date format, they should be prepared to format the field as date before entering. Similar for currency. Or at least make this sort of behavior optional. If the solution is restricted to only dates, the problem is only partially solved -- a complete solution involves the same section of code. (That is, number formatting.)
@regina with all due respect, the essence of the issue is automatic special formatting (be it dates, currency or whatever) interfering with a normal entry of data. There may be some users that prefer this behavior, but it should be optional, and preferably off by default. By the way, often ordinary numbers or texte + numbers is reformatted as dates when it doesn't even match any of the registered date formats. I've also encountered entries being unexpectedly converted to currency. (My locale is fr-CA.) e.g., if the calc date format is yyyy-mm-dd (using - as separator), entering 1.1.16 will be incorrectly formatted as the date 2001-01-16, even though it does not have the correct separators. Then changing the format to texte will result in some bizarre number, totally unrelated to what was entered. If someone wants a date format, they should be prepared to format the field as date before entering. Similar for currency. Or at least make this sort of behavior optional. If the solution is restricted to only dates, the problem is only partially solved -- a complete solution involves the same section of code. (That is, number formatting.) I would not call this issue an enhancement. In terms of normal use of a spreadsheet, it is an obvious bug.
@regina but why is this bug report marked as 'ENHANCEMENT'? I would say that a program that changes a number like '13.2' into a date and a postcode into a currency value must not just be enhanced but corrected... and by the way if this issue is corrected according to the interpretation of andr55, you can also close issue 87999 (marked as DEFECT). @egle what would Lithuanians type that they expect to be interpreted as their currency? same question regarding zip codes. would it be case sensitive? aren't there in Lithuania people who -like me- seldom use capital letters? exponential notation is something that makes me cautious: maybe in the solution to this series of bugs it would be best to offer switching on/off automatic interpretation of formatted data per format. maybe also offer adding/removing formats (I mean: list of formats has variable length) and allowing the user to specify the conversion rules. selecting national settings would activate the standard list of rules for that nation and no other (built in) rules would be applied.
Stupid and annoing thing! Fix it please!
This is driving me insane for years... I mean not just the obsession of calc by turning almost everything into date (while a single way "14/" would absolutely suffice me and would be welcome), but generally all the auto corrections. (For example capitalization of "x" is one of those things I have no more energy to fight with, I gave up) Because there's no Undo. Yes, we can discuss the formats which are "corrected" and should be not, or aren't and should be, but in the end, when you are using calc, no matter how smart the conversions are, you will hit a case when they are not welcome. At that point the missing Undo leads to epic fail.
reaction to ped's comments certainly, to automatically apply any correction assumes that 1) the correction will always be appropriate and 2) the user is not intelligent enough to learn the appropriate entry It seems to me to be much more appropriate to demand confirmation before applying each autocorrection. (This is already done for formulas in calc.) Doing so : 1) incorrect corrections will not be applied and 2) the user will learn the appropriate entries. Of course, having undo is a VERY useful feature, as any user could inadvertantly confirm an unwanted "correction" By the way, in my calc all autocorrections undoable under tools:autocorrection_options are undone, as few are wanted in the best of times. Unfortunately the automangling into dates of numbers under certain circumstances is not undoable. Yet. my 2ยข
Problem presists since 2004. Why is that? To discourage people from using Calc from OpenOffice (and use some commercial product instead)? This is a major bug, a blocker. I have work to do, and it can not be done with OpenOffice. Work is simple. I have lots of data tables in PDFs. They all should be copied and pasted for further manipulation. But every cell containing sequence like 01- is converted to date - not on display formatting level - but original value is DESTROYED (if the conversion was made only on display level - without touching original data - there would be no issue at all) Dear OpenOffice team. Why do you keep on ignoring since 2004 that we, the users, want to have AN OPTION of converting pasted data to dates - OR NOT! Do you really think you know better what should be done with user's data than the user himself? Do you realize, that there are other countries, you know, "outside USA". Not all the world uses the same date format. Microsoft knows that. There are no problems with dates in any Microsoft product - no matter which language you use. It's 2009 now. 5 years. Can you please add a small checkbox to configuration ("turn of automatic date recognition") in 3.1.2? For now I must quit using OO for a while and look for an other spreadsheet software to finish my job. Shame. BTW, questions about my OS or locale should be irrelevant here. This issue is not about INCORRECT date recognition. It's about UNWANTED date recognition.
If somebody likes to have autorecognition, it shoud be. But can you add a checkbox "turn off automatic date recognition" in configuration which switch off by default? And if I don't need it I'll can switch it off. Thanks.
It will be fixed for 3.3?
Shame indeed!
It seems to me that it would be better to store dates in the format yyyymmdd, much as is done in many accounting programs. Rarely are calculations done on dates. It is basically formated text. With this approach, formating something else as a date would be less of a problem. As well, dates being entrered or output would be handled more efficiently. (It takes much more processing to convert a text date to/from a number than to simply transform the text. Whenever one wishes to compare dates, if it is just a matter of deciding which is greater (or equal), it is just a text comparison, which is fast. If one wishes to calculate the days difference, the dates can be converted to some form of number. Even in accounting, the so-called 30-60-90 aging is actually often done as 1-2-3 calendar months, which could easily be done as a text comparison. (Probably why accounting programs have often stored dates as text.) So by storing dates as text instead of numbers, one gains in efficiency as well as reducing the impact of mis-recognition of text as dates. In any case, it is much better to either query conversion to dates (like is done for formulas) or to add an option to turn of automatic special formating -- of dates or whatever. THIS BUG NEEDS TO BE FIXED.
If set cell format "numeric" -1234 then if we write "1-12", "1/12" or "1.12"(!!!) then we see "40513". We have set the format "numeric" (no "date"), and entered text is converted first to a date, but after AutoCorrect to a number. It is not correct.
This is a very important bug, because the DATA INVISIBLY DAMAGED, (the data changes, not only wrongly displayed) "THIS BUG NEEDS TO BE FIXED".
Right priority for this bug is P1 - it is critical error, made calc useless.
I can't believe that this bug have not been fixed since 2004! It's the worst thing about OpenOffice. Please fix it :)!
^) Use libreoffice, this bug is fixed there ago :)
*** Issue 120383 has been marked as a duplicate of this issue. ***