Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||NF-DATE: Date format is not consistent, for ISO 8601 input a corresponding cell format should be applied and input line should match.|
|Component:||formatting||Assignee:||AOO issues mailing list <issues>|
|Status:||CONFIRMED ---||QA Contact:|
|Priority:||P4||CC:||apache.org-201110, erikanderson3, issues, jbf.faure, marcsinclair, mdxonefour, nesshof, njglin, roger.myjunk, stipanovich|
|Issue Type:||FEATURE||Latest Confirmation in:||---|
Description njglin 2002-06-04 20:50:45 UTC
When a cell date format is set to yyyy-mm-dd, the input line will show US format mm/dd/yyyy. Also, when the cell is doubleclicked it also changes to US format (from yyyy-mm-dd) until Enter is pressed. To reproduce: 1. Open a new spreadsheet. 2. Format a cell A1 to Date in the format yyyy-mm-dd - Highlight cell A1 - on the menu select "format->cells..." - select Category: Date Format : 1999-12-31 Language: Default Note: The Format Code shows YYYY-MM-DD 3. In the cell enter date 2002-06-04; press "Enter" 4. Highlight the cell again THE INPUT LINE SHOWS DATE AS: 06/04/2002 5. Doubleclick the cell THE CELL SHOWS DATE AS: 06/04/2002 A. The cell should ALWAYS show the date in the format set for the cell. B. The input line should ALWAYS show the date in the format set for the cell. My OS is Windows 2000 US version. The regional options are: -General - Your locale: English (United States) -Date - Short date format: yyyy-MM-dd - Long date format: dddd, dd MMMM, yyyy (since yyyy-mm-dd, dddd is not available)
Comment 1 peter.junge 2002-06-10 10:13:52 UTC
I'll have a look, Peter
Comment 2 peter.junge 2002-09-25 14:09:43 UTC
@Jacek: AFAIK the current behaviour is right and desired. In edit mode the cell always shows the default format because the user should see the exact content. Example: Fill two cells with your example (06/04/2002) and my example (04/13/2002) and format both as 'QQ YY'. If we would use the edit view you're suggesting you couldn't tell any difference between both cells. Peter
Comment 3 peter.junge 2003-02-10 16:33:13 UTC
no further comment -> closed
Comment 4 njglin 2005-03-09 22:21:19 UTC
Peter refers to my example wrongly. My example is 2004-04-29 format type. Just checked 2.0 Beta obtained from Openoffice.org(shows as Staroffice 8 Beta). Behaviour is the same as described in the problem description. In Short: If I format cells to use yyyy-MM-dd format then this format should be used and displayed consistently and always. Both in the cell, even when double clicked, as well as in the input line. I hope this bug can still make 2.0 release Trying to change a date when one format is displayed, but then another when trying to edit the cell is very trying.
Comment 5 frank 2005-03-31 08:08:15 UTC
*** Issue 46348 has been marked as a duplicate of this issue. ***
Comment 6 peter.junge 2005-09-14 16:53:57 UTC
Because this behaviour is currently intended it shoud be handled as requirement.
Comment 7 frank 2006-03-07 13:22:43 UTC
*** Issue 61720 has been marked as a duplicate of this issue. ***
Comment 8 frank 2007-09-07 10:07:20 UTC
*** Issue 79609 has been marked as a duplicate of this issue. ***
Comment 9 marcsinclair 2007-11-10 14:26:00 UTC
Hi, Is anything going to be done about this obvious flaw? at the very least the default format should be the machine locale setting. as it is the odd format used is at best annoying and practically unusable for most of the world, what a shame to spoil this software with such an amateurish problem. It's only been five years since the first report of the problem, any ideas on timescale? Marc
Comment 10 marcsinclair 2008-01-06 17:27:12 UTC
What is holding up the resolution of this annoying bug? If you need code, then kspread works correctly. It is open source and I'm sure you could see how it works. This is a BUG - not intended behaviour! can cause BIG problems when attempting to use dates, for example if a cell contains 2008-01-06 (the ISO date format) and I want to edit this date, the input line shows 06/01/2008 - What does this mean? Come on, it's one thing to admit that it is a problem and you're going to fix it, another to pretend that this is 'intended behaviour' It is problems like this that make me look stupid when I try to convert people from xl Regards
Comment 11 dwheeler 2008-05-05 18:34:57 UTC
Note: You _CAN_ enter dates in ISO format, and they ARE displayed on the spreadsheet correctly. The issue here is that when editing, dates are shown in U.S. format only.
Comment 12 marcsinclair 2008-05-06 01:21:25 UTC
No, the issue is that date handling in Open Office is a dogs breakfast. 1 - Open a new Spreadsheet on a machine with ISO dates as the native date format 2 - Enter the date 2008-01-02 into a cell 3 - Press return 4 - cell shows 02/01/08 (whatever that means, is it January, February or August?) 5 - Select the cell 6 - The Input line now says 02/01/2008 (aha! so it is either January or February) 7 - Double click the cell and it changes to the second format 02/01/2008 trying to edit in this unusual format is problematic and almost useless for practical use. Marc
Comment 13 kyoshida 2008-07-20 17:52:15 UTC
*** Issue 91856 has been marked as a duplicate of this issue. ***
Comment 14 ooo 2008-07-21 12:43:59 UTC
More descriptive summary.
Comment 15 seedsg 2008-10-18 01:58:52 UTC
As a new user to OO, imagine my sutprise when this straightforward and basic functionality in (forgive me) Excel is not available in OO. Imagine my dismay to discover further that this issue was first reported 6 years ago, and has had no action, in spite of having been re-discovered and reported independently by several people since then. As a minimum, why can't we add the option to display date cells in ISO format? Is it really that hard?
Comment 16 marcsinclair 2008-10-19 01:03:52 UTC
It's worse that that, you CAN display dates as ISO. but when you try editing them, and you are confronted by some unknown format, if you experiment, you can find out what it is (possibly mm/dd/yy) the default format seems not to be affected by the default locale setting of your machine so dates are largely unusable in open office calc. Sadly now Open office is so popular, that other open source spreadsheets appear to have been abandoned. My latest distro includes Open office calc as the ONLY spreadsheet, so if you don't use mm/dd/yy date format then you're scuppered.
Comment 17 ooo 2008-10-20 13:30:42 UTC
@marcsinclair: Please check again. The edit format _does_ follow the locale setting, it just does not follow a modified LC_TIME or Windows' Regional Setting's date mask, for example. Also, there are ways to override the locale: 1. Under Tools -> Options -> Language Settings -> Languages, the "Locale setting". If that is Default, the system's locale is used, else overridden. 2. In the Format -> Cells -> Numbers dialog the "Language" option. If that is Default, the locale from #1 is used, else overridden. And no, one does not always want to edit the date in the same format it is displayed, the ISO 8601 format is an exceptional case where that indeed is wanted.
Comment 18 marcsinclair 2008-10-20 22:23:47 UTC
I check every time there is a new release and I've just spent an hour trying it now on several machines, even on a windows machine. This machine (Mandriva 2009.0 64bit KDE 4) has ISO date set as standard, that is, most apps pick up that I have selected YYYY-MM-DD as the default long and short date. I open OOCalc (user interface language, set to default) I enter the date 2008-09-10 I leave the cell, the program changes the entry to 10/09/08 (whatever that means) I double click the entry to edit it the program changes the entry to 10/09/2008 (whatever that means) If I want to edit the date I have to 'guess what it means, so I enter 12/30/2008 (guessing that it is american) press enter oops, it's no longer a date, This is a dogs breakfast. It may have been acceptable to write applications like this at one time but this is a global world, (sic) All it needs is a box for the default date format, ISO will be the world standard one day, but I'd like to use it now.
Comment 19 corigo 2008-10-21 08:56:55 UTC
I think 72229 is a better answer to this problem.
Comment 20 marcsinclair 2008-10-21 20:26:56 UTC
Yes there is an important point made in issue 72229, that coupling a date format to a language makes very little sense. Whatever, the date format should be consistent from input to editing and from display to auto filling.
Comment 21 marcsinclair 2009-01-16 22:56:57 UTC
We are now in our Seventh year of this bug, any idea when OO will use ISO Standard Dates?
Comment 22 erikanderson3 2009-05-31 06:29:32 UTC
Forgive me, for I feel the need to flame here -- What, in the devil's briefcase, are you pinheads doing? This bug *cannot* be so complicated as to merit SEVEN YEARS with no visible progress. Meh. And people wonder why MS still has a lock on the office software market. </flame>
Comment 23 erikanderson3 2009-05-31 06:42:50 UTC
@er: > Please check again. The edit format _does_ follow the > locale setting, it just does not follow a modified LC_TIME > or Windows' Regional Setting's date mask, for example. This sounds like a bug in and of itself -- for aren't LC_TIME and date mask settings part of the locale settings? > Also, there are ways to override the locale: But the locale settings presented in either of the two OOo dialogs mentioned do not have any ISO listing, nor is there any way of finding the ideal combination of number, currency, and date formats aside from extensive trial and error. This is both tedious and onerously inefficient. > And no, one does not always want to edit the date in the > same format it is displayed, the ISO 8601 format is an > exceptional case where that indeed is wanted. Yet, setting up the default editing behaviour to use a separate unexplained format from the display format is both poor usability and an invitation to error. I would argue that the expected behaviour at least 90% of the time is for the editing and display format to match. Allowing an *option* for editing and display formats to be separate would be welcome. The current configuration is confusing and completely unacceptable. There is a reason other spreadsheet programs do not work this way.
Comment 24 Regina Henschel 2009-06-23 21:11:26 UTC
*** Issue 103041 has been marked as a duplicate of this issue. ***
Comment 25 Dotan Cohen 2009-06-24 12:13:22 UTC
There seems to be a lot of confusion here. Let's summarize: When editing a date in Calc, the date format used to edit the date does not match the date format configured for the cell, nor does it match the date format configured in the user's locale. This bug complains about the edit date format not matching the format configured for the cell. Another bug may or may not exist (if there is, then please link to it) which complains about the edit date format not matching the date format configured in the user's locale. A related bug (bug 72229) requests that the edit date format be configured separately from either the date format configured for the specific cell or from the user's locale date format. Steps to reproduce this bug: 1) Right-Click on a cell -> Format Cells -> Numbers -> Category -> Date -> Choose format 31/12/1999 2) Enter into that cell my wedding day: September 2, 2005: 2/9/2005 What is displayed? February 9, 2005: the day of a car bombing. What is expected to be displayed? September 2, 2005: my wedding day. Note that on this system the configured locale date format is yyyy-mm-dd.
Comment 26 Dotan Cohen 2009-06-24 15:11:02 UTC
Can someone with the proper permissions change the bug title to something clearer, like this: "Edit Date format does not match configured date format"
Comment 27 thebookkeeper 2009-11-13 14:51:40 UTC
The issue type and priority on this issue are wrong- issue type should be DEFECT (this is most certainly a bug) and as such priority should be bumped up to P3 since this issue is "non-trivial problem which probably affect a noticeable number of users." I find it mind boggling that a bug this annoying has persisted FOR SEVEN YEARS! Is there ever going to be some progress made on this? Other have adequately described the issue and what the behavior should be so I won't add to that fray, marcsinclair is absolutely right- this is dog's breakfast- it is confusing, inconsistent, and it should have been fixed years ago.
Comment 28 stippo 2009-11-13 15:22:07 UTC
Absolutely agree with "thebookkeeper" - this is not a "nice-to-have". It's absolutely imperative. I know I've delayed adoption of OO precisely because of this extremely sloppy attention to an important, nay critical, issue. It's obviously stuck in an American format. You folks responsible might want to pull your heads out of ... the ground, take a look around and realize there's a whole world out there that does things differently - and more logically.
Comment 29 jbf.faure 2009-11-14 07:02:07 UTC
I like current behaviour: when I need dates I prefer format NNNNJ MMMM AAAA and it not very easy to type "dimanche 15 novembre 2009" instead of 15/11/09 as I should do if I had to type the same thing that which is displayed. I suggest to give the user the possibility to define an "input date format" which have to be distinguished from "display date format". Input date format would be defined in Tools > Options > Language settings > Languages, in similar way than decimal separator, but with an input field and a default value equal to the locale standard. It should be also important that this input date format would be displayed (read only) in each dialog where the user can define a display date format, so that he would be aware of the difference.
Comment 30 Dotan Cohen 2009-11-14 14:32:38 UTC
@jbfaure: Please do not hijack this bug. There already is an open feature request for having the displayed date format be different then the date format used to enter the data. This bug discusses a programming error, not a feature request.
Comment 31 ooo 2010-01-21 18:48:32 UTC
Comment 32 marcsinclair 2010-01-22 18:14:25 UTC
NO WAY is this a 'feature' issue, this is a BUG - plain and simple, it is a joke that someone can come along and sideline this VERY important issue. Read the thread and try it for yourself, there is NO WAY that this behaviour was intended, think about it.
Comment 33 stippo 2010-01-22 19:57:40 UTC
Couldn't agree more with Marc but I think we're voices crying in the wilderness. It's kept me from using calc for over 6 years while desperately wanting to support OO. BTW - this is not just a "display" issue. Date calculations depend on consistent date formatting. I guess we're stuck with Excel whether we like it or not.
Comment 34 roger6106 2010-03-13 20:37:50 UTC
I am moving from the United States to Canada. I switched my computer over to Candian formats, and the system-wide setting is yyyy-mm-dd. However, Openoffice.org insists on using dd/mm/yy when I edit a date cell or add a new date. This is confusing since Canada uses dd/mm/yy, mm/dd/yy, and yyyy-mm-dd. Please make OpenOffice.org follow the system setting for dates.
Comment 35 marcsinclair 2010-03-14 14:11:24 UTC
I've just downloaded oo 3.2, and surprise surprise, still has the same BUG. My feeling is that there is no real point in pursuing this bug. It appears that as far as the open office maintainers are concerned, as long as everything works OK for US users then then there is no problem. This issue is not a complex one, the date format should follow whatever the USER wants, whether that is the system-wide setting or a custom setting on a per-sheet or per-cell setting. and when that date is edited it should appear in the edit location as that format.
Comment 36 ooo 2010-03-15 15:19:17 UTC
@roger6106: For Canadian locales see issue 106992 that is in the queue for OOo3.3
Comment 37 stippo 2010-03-15 16:29:47 UTC
First of all, this is not a "Canadian locale" issue. The reference to issue 106992 is another goose chase that resolves nothing and understands nil about user requirements. Just for the record "ISO" stands for International Standards Organization. "International" - that means the world. And the world doesn't use outdated, confusing, ambiguous U.S. standards. As long as 4.54% of the world's population insists that its standards are the only standards and the rest of the world needs "workarounds" Calc will never be useful to the other 95.46%. Give your heads a shake.
Comment 38 pkidwell 2010-05-03 14:08:14 UTC
I don't see this as any different than having a numeric value such as 1.23456789 in a cell, formatted to display as 1.235. When you edit it, you will see 1.23456789; to edit it as it was formatted, you may lose data (in this case, 5 decimal places). Likewise, to pj's original comment so long ago, if you had formatted the date as "QQ YYYY", and edited it according to the format, you would only be able to change the quarter or the year, not the actual date in the cell. This is why editing and display should remain separate. That said, the problem seems to really be that the date editor, whether by double-click or in the entry line, defaults to US date format, regardless of anything else. It seems that Issue 72229 really is the answer - for all date cells, let the user pick the format that should be used for editing.
Comment 39 Dotan Cohen 2010-05-03 15:18:08 UTC
> I don't see this as any different than having a numeric value such > as 1.23456789 in a cell, formatted to display as 1.235 No, it is akin to having 1.23456789 in a cell, formatted to display as 235.1 Now do you see why it is bad?
Comment 40 niklas.nebel 2010-05-03 16:30:10 UTC
Editing does not default to US format. It takes the locale from the cell's number format, which by default is the system locale, and then uses that locale's date-editing format, as defined in our list of locale data. As indicated by the summary, this issue is only about special treatment for ISO 8601 format.
Comment 41 Dotan Cohen 2010-05-03 22:03:01 UTC
> Editing does not default to US format. I concede that you are right. On my test system the locale was configured for LANG=en_US.utf8 and LC_TIME=en_DK.utf8. Changing the LC_TIME did not affect the cell's number format, but changing LANG did. To clarify: when entering "YYYY-MM-DD" into a cell, the formatting of the cell for both display and for inline editing became that associated with LANG: "DD/MM/YY" for en_US and "DD/MM/YY" for he_IL. The formula bar showed YYYY in place of YY. Therefore, one issue is that OOo is using LANG for setting time, not using LC_TIME. I will search for a bug on that and report it if necessary. > It takes the locale from the > cell's number format, which by default is the system locale, and then > uses that locale's date-editing format, as defined in our list of > locale data. Our list? Where is that list? Why isn't LC_TIME being used for this? > As indicated by the summary, this issue is only about special treatment > for ISO 8601 format. This is in fact the format that I am interested in. Quick experimenting in Calc shows that only the Hungarian and Lithuanian locales in OOo use the YYYY-MM-DD format, and they both suffer from other issues where I would not use them. I suppose that a dedicated ISO 8601 locale would be a workaround, but the fix would involve (as I see it) using LC_TIME for the editing format. Thanks.
Comment 42 ooo 2010-05-04 11:55:26 UTC
Regarding LC_* that's issue 102517. OOo's known locales are in i18npool/source/localedata/data/*.xml This issue here is about applying an ISO 8601 format if the input was detected as such and editing in ISO 8601 format in any locale, not about an ISO 8601 locale.
Comment 43 marcsinclair 2010-05-16 15:58:44 UTC
nn wrote >Editing does not default to US format. It takes the locale from the cell's >number format, which by default is the system locale, and then uses that >locale's date-editing format, as defined in our list of locale data. Simply not true, have you actually tried it? My pc is set to ISO dates, and the cells are formatted to ISO and still the editing line uses another format.
Comment 44 ooo 2010-05-17 10:24:24 UTC
@marcsinclair: The edit format is taken from the _locale_, not the system's user-specified settings (which is another RFE).
Comment 45 thetrivialstuff 2010-06-06 09:59:07 UTC
OK, I'm late joining in on this bug, but it's annoyed me for many years. I thought I understood what was really going on here, but it appears I don't. Here's me being as clear as I can be: - I want ISO 8601 dates in OO Calc. I want to be able to *edit cells* in ISO 8601. - According to the "this is not a bug" camp, when editing date values OO uses the system locale. - I edited my system locale files to use ISO 8601 for en_GB (my preferred locale), ran locale-gen, and logged off and back on. - Other programs are using ISO 8601 now, so I think I did it correctly. - OO Calc is still using the original en_GB date format! (At least it's not en_US...) So, either I've missed something while editing my locale files, or OpenOffice is using its own copies of the various locales. I really hope that's not the case, and I fervently hope that that's not by design if it is. Doesn't that completely break how locales are supposed to work? Can someone help me out here? WHAT DO I NEED TO DO TO GET ISO 8601 DATES IN CELL EDITING? And more importantly, WHAT DO I NEED TO DO TO GET IT IN ENGLISH? If this is not a bug, answer those two questions, please. If there is no way to get ISO 8601 in English, THAT IS A BUG. End of debate, and someone needs to fix it. (BTW, I already tried setting OO to Hungarian -- it has a two-digit year, so that won't do. Lithuanian... well, we'll see. I was amused to see that in the default format, today's date comes out as "2010 metų birželio mėnesio 6 diena".) It also bears pointing out that in various places in the OO user interface, dates are shown in American format regardless of the program or system locale (have a look at the Calc > Calculate options, for instance). That might explain some of the deafness this bug appears to be falling on - the developers are comfortable with month-day-year, and don't understand why anyone would want anything else. So, here's a brief list of things ISO 8601 can do that month-day-year can't: - be unambiguous. - sort lexicographically (e.g. 2009-02-13 comes before 2010-01-13 even when you're sorting as text, whereas m-d-y would give you ). - avoid y2k problems (a lot of people using m-d-y still seem to write only two digits for the year -- which is part of this bug, too). - be consistent with how you write times -- larger units go in front of smaller ones. You wouldn't like a watch that formatted minutes:seconds:hours, so why would write your dates that way? - if you only care about the year and month, or only the year, you can chop off the rest and it's still unambiguous: 2010-06, or 2010. Give it a try; you might like it. (But you might have to fix this bug first if you want to try it in OpenOffice ;) )
Comment 46 Dotan Cohen 2011-12-21 19:52:06 UTC
This bug has been fixed by Eike Rathke in LibreOffice, the OOo fork: https://bugs.freedesktop.org/show_bug.cgi?id=37978