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.)

@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.)

@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
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. ***