Issue 33723

Summary: NF-DATE: Ability to turn off Number Recognition for incomplete dates in Calc
Product: Calc Reporter: aaronforjesus <aaronforjesus>
Component: editingAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: az77, cno, discoleo, issues, jacob, kozodaevroman, leha2000, mantas, ooo, pagalmes.lists, rb.henschel, schnell4us, stp, xepecine
Version: 680m51   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---
Issue Depends on:    
Issue Blocks: 13671    
Attachments:
Description Flags
sample writer document that contains a table with data that needs to be copied into calc
none
screenshot - conversion to dates
none
Screen Shot of Calc Import Dialog none

Description aaronforjesus 2004-09-02 01:56:00 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)
Comment 1 frank 2004-09-02 15:12:19 UTC
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 ***
Comment 2 frank 2004-09-02 15:12:39 UTC
closed double
Comment 3 aaronforjesus 2005-01-26 23:52:58 UTC
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.
Comment 4 frank 2005-01-27 10:22:56 UTC
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 ***
Comment 5 frank 2005-01-27 10:23:14 UTC
closed again as double
Comment 6 aaronforjesus 2005-01-27 19:05:15 UTC
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.
Comment 7 frank 2005-01-28 10:45:34 UTC
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
Comment 8 aaronforjesus 2005-01-28 15:04:35 UTC
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.
Comment 9 aaronforjesus 2005-01-28 15:05:52 UTC
Created attachment 21963 [details]
sample writer document that contains a table with data that needs to be copied into calc
Comment 10 aaronforjesus 2005-01-28 15:08:49 UTC
I think someone else mentioned that in Excel, if you click undo, it will undo
the autoformat leaving the original data that was pasted.
Comment 11 falko.tesch 2005-10-20 20:25:26 UTC
FT: Re-assigned to requirement default user
Comment 12 Rainer Bielefeld 2006-12-11 09:56:16 UTC
*** Issue 72449 has been marked as a duplicate of this issue. ***
Comment 13 discoleo 2008-04-29 18:29:13 UTC
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.
Comment 14 frank.loehmann 2008-05-22 09:34:21 UTC
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.
Comment 15 cno 2008-07-14 14:57:32 UTC
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?
Comment 16 ooo 2008-09-03 12:25:31 UTC
You don't want to turn off number or time recognition, but incomplete dates.
Adapted summary.
Comment 17 az77 2009-01-12 03:23:08 UTC
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.) 
Comment 18 fijam 2009-01-18 14:12:51 UTC
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.
Comment 19 tomwb 2009-01-18 15:02:37 UTC
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
Comment 20 fijam 2009-01-18 20:37:03 UTC
Created attachment 59461 [details]
screenshot - conversion to dates
Comment 21 fijam 2009-01-18 20:37:42 UTC
Indeed, it does not.

I attached a screenshot to better illustrate the problem. 

Windows XP SP3, OOO300m9 build:9358
Comment 22 tomwb 2009-01-19 02:37:30 UTC
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
Comment 23 mfrasca 2009-01-19 08:31:46 UTC
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!
Comment 24 fijam 2009-01-19 08:43:21 UTC
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). 
Comment 25 tomwb 2009-01-19 11:07:05 UTC
Created attachment 59474 [details]
Screen Shot of Calc Import Dialog
Comment 26 tomwb 2009-01-19 11:14:17 UTC
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
Comment 27 egle 2009-05-07 20:37:14 UTC
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?
Comment 28 Regina Henschel 2009-05-07 21:22:47 UTC
@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.
Comment 29 az77 2009-05-08 13:09:04 UTC
@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.)
Comment 30 az77 2009-05-08 13:10:53 UTC
@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.)
Comment 31 az77 2009-05-08 13:13:08 UTC
@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.)
Comment 32 az77 2009-05-08 13:14:36 UTC
@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.)
Comment 33 az77 2009-05-08 13:17:36 UTC
@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.)
Comment 34 az77 2009-05-08 13:26:39 UTC
@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.
Comment 35 az77 2009-05-08 13:28:57 UTC
@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.
Comment 36 mfrasca 2009-05-08 14:25:27 UTC
@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.
Comment 37 vladsavitsky 2009-07-21 13:32:39 UTC
Stupid and annoing thing!
Fix it please!
Comment 38 ped 2009-08-19 09:18:22 UTC
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.
Comment 39 az77 2009-08-19 19:21:32 UTC
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ยข
Comment 40 htd 2009-09-22 18:57:07 UTC
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.
Comment 41 bodrisch 2009-09-23 14:13:38 UTC
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.
Comment 42 mr_smyle 2010-02-19 12:17:40 UTC
It will be fixed for 3.3?
Comment 43 fobf 2010-06-09 11:18:32 UTC
Shame indeed!
Comment 44 az77 2010-06-10 08:06:04 UTC
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.
Comment 45 useronforum 2010-11-05 01:16:28 UTC
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.
Comment 46 useronforum 2010-11-05 01:19:56 UTC
This is a very important bug, because the DATA INVISIBLY DAMAGED, 
(the data changes, not only wrongly displayed)

"THIS BUG NEEDS TO BE FIXED".
Comment 47 adamecm 2010-11-05 01:23:34 UTC
Right priority for this bug is P1 - it is critical error, made calc useless.
Comment 48 Nick 2013-03-05 10:00:00 UTC
I can't believe that this bug have not been fixed since 2004! 
It's the worst thing about OpenOffice. 

Please fix it :)!
Comment 49 leha2000 2013-03-28 20:57:23 UTC
^)
Use libreoffice, this bug is fixed there ago :)
Comment 50 Edwin Sharp 2013-11-20 21:22:22 UTC
*** Issue 120383 has been marked as a duplicate of this issue. ***