Bug 34724 - EDIT: unintended date recognition of input Jan1 or 1Jan
Summary: EDIT: unintended date recognition of input Jan1 or 1Jan
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: numberformat target:4.3.0
Keywords:
Depends on:
Blocks:
 
Reported: 2011-02-25 07:51 UTC by Jiri
Modified: 2014-05-20 08:35 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Jiri 2011-02-25 07:51:55 UTC
in Calc when I enter text e.g. I1 into an empty cell it is changed to 1.1.2001
and it is not possible to change it untill 'I1 entered.

I was said it is a feature of Calc. It works with abbreviated month and day names as defined in the locale file.
But both in Czech country and in Slovakia Nobody writes I12 to get 12.01.2011

Similar behaviour was in OOo, which I removed by deleting "I II III IV ..." from the lists definitions.

Thershould be a switch to get rid of that "feature".
Thanks
Comment 1 Don't use this account, use tml@iki.fi 2011-03-08 00:02:15 UTC
Kohei, do you know of any easy workaround, or have any knowledge if this is going to be fixed at some point?
Comment 2 Cor Nouws 2011-03-08 04:09:24 UTC
I'm not kohei, but for reference I mention the following:
http://openoffice.org/bugzilla/show_bug.cgi?id=33723
http://openoffice.org/bugzilla/show_bug.cgi?id=87999 (kohei is in cc here)
and implemented from 3.3.0 by kohei
http://wiki.services.openoffice.org/wiki/Calc/Features/Numbers_import_for_plain_text_files
Comment 3 Kohei Yoshida 2011-03-08 05:27:58 UTC
Just set the cell format to Text, and all number recognition will be turned off.  That's how it's supposed to work.
Comment 4 Kohei Yoshida 2011-03-08 05:35:44 UTC
So, in essence,

1. We can't turn off number recognition for default cells.  We can improve the automatic recognition, but we can't turn it off.
2. Setting the cell format to Text prior to entering a value turns off automatic number recognition.  That's an intended feature which can be used to circumvent this problem.  Putting ' in front was the old workaround.

Unfortunately it's not easy to improve auto recognition since the number format code is full of hacks piled up in many layers accumulated over many years.  So, please don't hold your breath for any solution anytime soon.
Comment 5 khagaroth 2011-07-31 02:59:03 UTC
This feature definitely needs to be killed off. It's completely useless, I haven't seen one person that would actually use this, but everyone, and I mean _everyone_ is pretty mad about it. It's orders of magnitude more harmful than it is useful. It doesn't apply only to Calc btw., it's the same with text tables in Writer.
I could live with it, if the conversion was applied only to cells that were set to date format beforehand, but doing this conversion everywhere is pretty much insane.
Comment 6 Kohei Yoshida 2011-08-01 07:52:58 UTC
Setting this back to the default ownership.
Comment 7 Björn Michaelsen 2011-12-23 11:51:13 UTC
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:
http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Comment 8 sasha.libreoffice 2012-02-24 23:17:35 UTC
@ Jiri
In 3.5.0 version problem still exist? (it can be locale specific, so I can not verify)
Comment 9 Jiri 2012-02-25 15:23:13 UTC
Yes, this behaviour persists in 3.5.0
Comment 10 ign_christian 2013-06-25 03:25:33 UTC
Not reproducible on LO 4.0.4.2 (Win7 32bit) with standard installation (English)
Comment 11 sasha.libreoffice 2013-06-25 06:58:00 UTC
In English should be entered something like jan1 to reproduce this bug.
Problem is that in Czech and in Slovakia (as author told) Calc understands Roman numbers as names of months.

Question to developers:
How Calc determines which list is months names and which is not? And how change it? For example I with to add my own list of month names and it should autodetected as date.
Comment 12 andréb 2013-07-09 23:38:03 UTC
This problem, automatic translation to date of text in default formatted cell, still exists in Libo 3.5.7.2
I use the french locale (on Linux), and it doesn't even translate into a date format that I use.
Worse still, when the format is reset to text, one gets a number not at all related to the original text.

The only reasonable solution is to turn off all automatic (attempts to) date recognition.

For the small minority that would appreciate this function, there could be a non-default option.  If such an option is activated, the type of conversion should be configurable (it chooses the wrong date format in my case), and also explicitly reformatting the cell as text should revert to the text entered.

A date in spreadsheet context is a form of text, not a number intended for calculation.  The fact that dates are normally stored in number format should not affect the automatic treatment of values entered.

This bug (at the very least in conception) has existed for years, going back to Openoffice days, as well as the refusal to fix it.  Since when does one normally do calculations on dates ?
Comment 13 andréb 2013-07-10 05:02:55 UTC
(In reply to comment #4)
> 1. We can't turn off number recognition for default cells.  We can improve
> the automatic recognition, but we can't turn it off.

This issue has nothing to do with automatic number recognition.
Rather it is about *not* automatically turning non-numeric text (or possibly numeric text) into dates.  Dates do *not* have to be stored as numbers, and they are not displayed as such either.  In any case, they are not normally entered as numbers.
The only advantage of storing dates as numbers is to more readily compare the difference of 2 dates in days.  Storing in ISO format allows relative comparison and is more compact and allows faster display.  Many applications store dates in ISO-equivalent format.

(Which reminds me of another idiosyncracy.  In cells without a specific alignment format, entries beginning with with a number should not be right-aligned, but rather use the default global alignment, which is usually to the left.  There is no way of knowing if entries are numbers until all characters are entered, and even for numbers it is less confusing to see it left-aligned during entry.  Try entering a few hundred fields by hand to see.  That could be made an option as well.)

> 2. Setting the cell format to Text prior to entering a value turns off
> automatic number recognition.  That's an intended feature which can be used
> to circumvent this problem.  Putting ' in front was the old workaround.

Please see response to point 1.

> Unfortunately it's not easy to improve auto recognition since the number
> format code is full of hacks piled up in many layers accumulated over many
> years.  So, please don't hold your breath for any solution anytime soon.

However, it should be very easy to turn off automatic conversion to dates.
Attempting automatic date recognition is evidently a complex process (which is not currently well done), so turning it off would simplify calc code.

Any date recognition code, to be readily maintainable, should be cleanly separated from other code.  If one chooses to keep such code as a non-default option, the option flag could be used to separate it from other code.
Comment 14 bfoman (inactive) 2013-08-15 15:15:12 UTC
Does locale dependent date acceptance patterns for input of incomplete dates introduced in 3.6 change anything?
See http://erack.org/blog/archives/8-LibreOffice-date-acceptance-patterns.html
Comment 15 Stanislav Horacek 2014-03-19 21:26:59 UTC
The patterns are a great option, but they don't affect this problem – the "i1" string is still converted to the 1st January 2001.
Comment 16 Eike Rathke 2014-05-20 00:18:15 UTC
I'm working on this. Actually there isn't anything to be made optional, we just don't want a 1Jan or Jan1 string without any separating character to be accepted as date.
Comment 17 Commit Notification 2014-05-20 08:35:24 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=43624d9370c4384f71c6b13fed900eaec222cf64

resolved fdo#34724 Jan1 or 1Jan without separating character is not date



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.