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".
Kohei, do you know of any easy workaround, or have any knowledge if this is going to be fixed at some point?
I'm not kohei, but for reference I mention the following:
http://openoffice.org/bugzilla/show_bug.cgi?id=87999 (kohei is in cc here)
and implemented from 3.3.0 by kohei
Just set the cell format to Text, and all number recognition will be turned off. That's how it's supposed to work.
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.
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.
Setting this back to the default ownership.
[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:
more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
In 3.5.0 version problem still exist? (it can be locale specific, so I can not verify)
Yes, this behaviour persists in 3.5.0
Not reproducible on LO 126.96.36.199 (Win7 32bit) with standard installation (English)
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.
This problem, automatic translation to date of text in default formatted cell, still exists in Libo 188.8.131.52
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 ?
(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.
Does locale dependent date acceptance patterns for input of incomplete dates introduced in 3.6 change anything?
The patterns are a great option, but they don't affect this problem – the "i1" string is still converted to the 1st January 2001.
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.
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":
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:
Affected users are encouraged to test the fix and report feedback.