Following from German View, but with common importance:
Everything only for date formatted cells
All examples July 2012, German Localization, Cells in German Language
All Tests with Server Installation of "LibreOffice 22.214.171.124 rc German UI/Locale [Build-ID: 815c576] on German WIN7 Home Premium (64bit)
Until 3.5 an input MM-DD (12-31) has been accepted for 2012-December-30, has been recognized and converted to selected formatting of cell.
In 3.6.0. This input no longer will be accepted, and that makes Calc unusable for users who often have to do date inputs (trading, ...) in countries where the numeric keyboard has a comma, not a dot.
I just see that my main did some exception handling without involving my brain ;-)
T thought to type MM-DD (12-31) with 3.5. but actually I type DD-MM (31-12). Although I have become really used to do so, I would prefer some ISO8601 truncated input MM-DD, and I would have no problem at all to change my typing
Since I started thinking about all this I now have problems with date typing with 3.5.5 ....
This really is something for ux-advise!
You example is not correct.
Until 3.5 (and all Excel versions since 4.0, Staroffice and OpenOffice versions), the input (using the german locale) was tried to be interpreted as as DD-MM: therefore 12-31 resulted in a string "12-31", but 31-12 resulted in 31.12.2012 (31st December of the current year).
In German language it is common to say a date is on "the 31th of march" or at the "31st 3rd" (normally in german ;-) ) when you are speaking about the current year. So everything is consistent except the fact that minusses are used to seperate the values.
Until 3.5 it didn't play any role if minuses or dots have been used for seperation.
IMHO for date formatted cells we should have a very aggressive date recognition, may be to be activated by an extra checkbox. Of course it will not be easy to get a quick solution for all localizations, but IMHO there exit some very plausible assumptions.
a) Users who are very interested in this feature are experienced, use well
prepared and so well formatted spreadsheet templates
b) These templates have well defined cells only used for a particular kind of
data. For example a cash account book has a column for
- amount of money, only currency inputs
- income date, only date values
- payed date: only date inputs
- subject, only text inputs.
c) In these columns it is clear that the only exception for inputs differing
from the selected formatting can be a text, no date input makes sense in
a currency field and vice versa.
d) so for a date field you can assume:
da) A single number always will represent the Day number of the current
Month of the current year
db) 2 numbers separated by an arbitrary separator always will represent the
Day number and the Month of the current year
dc) 3 numbers separated by the same arbitrary separator always will represent
Day and Month and current year
e) in a document related property nearby the "activate aggressive date
recognition user has to define whether such inputs
have to be uninterpreted as
- Y M D (ISO, default)
- D M Y
- M C Y
(I do not believe that other ones are used very often?)
f) For all other inputs and for cells not formatted as date the more
restrictive date recognition started in LibO 3.6 will be used.
Looks to me that you are trying harder and harder to turn people away from LO.
You took a simple feature which exists for who knows how many years in all Office suites and started making nuclear fysics out of it.
I have about 300 LO users in my company and they are ALL used to enter date as 31.08 in cells which don't have date format (they have general format).
Why are you trying to remove feature which is good and force upon users feature which is slower and worse? Where is the sense in that?
(In reply to comment #3)
> IMHO for date formatted cells we should have a very aggressive date
> recognition, may be to be activated by an extra checkbox.
I agree completely with Rainer. But NB: "for date formatted cells" only, right!
In my eyes, this is a valid and very reasonable enhancement request, therefore I change the status of this report to NEW.
(In reply to comment #4)
> I have about 300 LO users in my company and they are ALL used to enter date as
> 31.08 in cells which don't have date format (they have general format).
Please let me ask a simple question, because I just don’t understand why the situation seems so clear to you.
If a cell has date format, "31.08" should be recognized as August 31, of course. It might also be reasonable to recognize "31.08." (NB the final dot) as August 31 even in cells of general format. But if a cell has both general format and contains "31.08" without the final dot -- why should "31.08" then be recognized as August 31? It could be equally well be recognized as 31.08 (the numeric value), and IMHO this would be even more reasonable ...
There are even German users who prefer '.' as decimal separator, '.' is the decimal separator in most languages of the world, and if you would present to me a paper with the figures "31.08" on it and ask me to guess what it means, I would just say: "Most probably the numeric value 31.08; maybe a date (August 31), but the final dot is missing, and therefore this is very improbable".
> If a cell has date format, "31.08" should be recognized as August 31, of
> course. It might also be reasonable to recognize "31.08."
I have nothing against all date recognition on date formated cells. You can even recognise 31,08, as 31.08 of this year for all I care. That would even be nice feature since some keyboards (croatian style for example) have comma , instead of dot . on numerical keyboard between 0 and ENTER.
> But if a cell has both general format and contains "31.08" without the final
> dot -- why should "31.08" then be recognized as August 31? It could be equally
> well be recognized as 31.08 (the numeric value)
In croatian language, dot . is used only as thousand separator, never as decimal separator. And dot . is never used on date endings (31.08. is wrong, 31.08.2012 is right). So, if I enter 31.8 or 31.08 you cannot make a mistake with recognising it as date and entering 31.08.2012 as value in general formated cell.
For example, in Excel 2003 (newer versions probably behave same) if you enter 31.08.2012. (with dot at end) Excel will not recognise it as date even with date formatted cell.
But why do I need to explain all that, you have it already is 3.5.6 version and you started tempering with it for unknown reason. You won't increase intuitive usage for users, it will be worse. You won't decrease time needed for user to enter date, you'll increase it. All in all, you'll make whole experience longer and less intuitive and you still think it's good thing to do.
> There are even German users who prefer '.' as decimal separator, '.' is the
> decimal separator in most languages of the world...
There are probably croatian users which preffer dot . instead of , because they do accounting for english speaking area, but they can always change their preferences and enable dot as decimal separator.
Also, user in bug 52240 which tested 3.6.1 version complains that even in date formatted cells 16.8 isn't recognised as 16.8.2012 .
Any thoughts Eike ? :-) (now a MAB)
(In reply to comment #6)
> In croatian language, dot . is used only as thousand separator, never as
> decimal separator. And dot . is never used on date endings (31.08. is wrong,
> 31.08.2012 is right). So, if I enter 31.8 or 31.08 you cannot make a mistake
> with recognising it as date and entering 31.08.2012 as value in general
> formated cell.
I may have overseen it, but you never mentioned here that you are talking specifically about the Croation locale. So let us assume that the date recognition you are talking about (to recognize "31.8" even in general fields as date) should be enabled only when the language of the document/cell is Croation. If it is true that in the Croatian language "31.8" is a valid and usual way to write August 31, then you are right: it would be reasonable to recognize "31.8" as August 31, even in general fields.
So we all need to recognize how important the locale is. When you write:
> 31.08. is wrong,
this may hold true for Croation (I just don’t know); in German, e.g., it is completely different: "31.8" is not a valid way to write a date, we *always* need the periods after the day number and after the month number. This may explain the misunderstanding why I (and other users) don’t understand why you insist on recognizing "31.8" as a date so heavily; and on the other hand, it explains why you don’t understand that we don’t understand that ;-).
So, let’s state the following result: the locale, I mean, the current language setting for the document (or the part of the document we are editing), is very important and needs to be honored also for these special questions of date recognition. This is already done by LibreOffice in many respects, but not in all, as you example of "31.8" shows.
> For example, in Excel 2003 (newer versions probably behave same) if you enter
> 31.08.2012. (with dot at end) Excel will not recognise it as date even with
> date formatted cell.
This is not disputed, and I never said anything like that; after the year number, even in German is never placed a period.
Could we please stick to the original topic of this bug, which is about allowing a hyphen as date separator if the locale's separator is something else to allow date input on numeric keypad, and not mix in various other observations? Thank you.
Related issues of abbreviated date input were already discussed in bug 52240 and especially for locales using the '/' separator already solved.
(In reply to comment #10)
> Could we please stick to the original topic of this bug [...]
Could you please file a separate enhancement request including your considerations from comment #3? They should not get lost here. And CC me for this new report, please! Thank you!
Please add your request for support of Croation date format "31.08" either to bug 52240, if necessary (don’t forget to mention that this is a common date format in Croatian), or file a new bug report for it which should then "block" bug 52240. Thank you!
implements user editable date acceptance patterns under Tools->Options->LanguageSettings->Languages
For the specific problem mentioned in this bug the patterns could be augmented from D.M.Y;D.M. to
If 3-4 shall not result in a date, D-M- could be used instead of D-M
Note that to enter an ISO 8601 Y-M-D date with a D-M-Y pattern active one needs to enter a year >31 or with at least 3 digits, e.g. 011
That sounds good! Thank you!
A suggestion: I'd like to see the "old" behaviour as a default: Power-users and people who migrate from all the other spreadsheet-systems can continue working as they always did. Those who don't like the dates to be recognized could remove the patterns.
Dear bug reporters,
Eike has suggested this bug to be fixed as a late feature in 3.6 with:
to be sure, there are no regression from this, please test a daily build from:
and see if you see any trouble with the fix.
(In reply to comment #13)
> A suggestion: I'd like to see the "old" behaviour as a default:
Maybe in a future release.
Sticking with de-DE as example that would mean to have at least these patterns:
(plus in theory any combination of ./- separators, it was accepted but insane and not needed) though D/M/ and D-M- with the trailing separator probably could be omitted in the de-DE case but generally we don't know for each locale.
Patterns would have to be added separately for each locale, for example for en-US locale a M.D does not make sense because '.' dot is decimal separator, but M.D. and M.D.Y still might (regarding old behavior).
Then again for locales that use ',' comma as decimal separator and '/' as date separator a D.M does not make sense either, but did people use it for dates or were they annoyed by a section numbering being treated as date? Also, a D-M leads to unwanted date for a range as in 1-5 for locales that do not use '-' separator, and in en-US 1.2.3 may as well be some section numbering. I'm not sure if we really want to reintroduce all these cases.
Anyhow, this needs some thoughts and the default patterns should be generated during building the locale data and not added manually.
And please, if anyone wants to discuss this, please do it on the l10n mailing list and not in this bug.