[Upstream] Edit Date format does not match configured date format

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)

I'll have a look, Peter

@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

no further comment -> closed

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.

*** Issue 46348 has been marked as a duplicate of this issue. ***

Because this behaviour is currently intended it shoud be handled as requirement.

*** Issue 61720 has been marked as a duplicate of this issue. ***

*** Issue 79609 has been marked as a duplicate of this issue. ***

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

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

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.

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

*** Issue 91856 has been marked as a duplicate of this issue. ***

More descriptive summary.

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?

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.

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

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.

I think 72229 is a better answer to this problem.

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.

We are now in our Seventh year of this bug, any idea when OO will use ISO
Standard Dates?

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>

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

*** Issue 103041 has been marked as a duplicate of this issue. ***

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.

Can someone with the proper permissions change the bug title to something
clearer, like this:
"Edit Date format does not match configured date format"

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.

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.

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.

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

Grabbing issue.

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.

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.

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.

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.

@roger6106: For Canadian locales see issue 106992 that is in the queue for OOo3.3

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.

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.

> 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?

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.

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

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.

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.

@marcsinclair: The edit format is taken from the _locale_, not the system's
user-specified settings (which is another RFE).

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

After nearly TEN YEAR of this BUG in Open Office - perhaps Libreoffice has the will to do something about it -

The issue is that date handling in the spreadsheet is a dogs breakfast.

1 - Open a new Spreadsheet on a machine with ISO dates as the native date
format

2 - Enter the (valid) date 2011-06-05into a cell

3 - Press return

4 - cell shows 05/06/11 (whatever that means, is it May, June or
November?)

5 - Select the cell

6 - The Input line now says 05/06/2011 (aha! so it is either May or June)

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

Marc

After nearly TEN YEAR of this BUG in Open Office - perhaps Libreoffice has the
will to do something about it -

The issue is that date handling in the spreadsheet is a dogs breakfast.

1 - Open a new Spreadsheet on a machine with ISO dates as the native date
format

2 - Enter the (valid) date 2011-06-05into a cell

3 - Press return

4 - cell shows 05/06/11 (whatever that means, is it May, June or
November?)

5 - Select the cell

6 - The Input line now says 05/06/2011 (aha! so it is either May or June)

7 - Double click the cell and it changes to the second format 05/06/2011#
trying to edit in this unusual format is problematic and almost useless for
practical purposes.

Marc

*** Bug 41042 has been marked as a duplicate of this bug. ***

Fixed in master
http://cgit.freedesktop.org/libreoffice/core/commit/?id=6c37252eb93db161edfd234696604774e9ee1d8c
and 3-5
http://cgit.freedesktop.org/libreoffice/core/commit/?h=libreoffice-3-5&id=9c1d304d0df21b6ae63dbd4db737f0d21a51f227

> Fixed in master
>

I cannot believe it! This has been one of three or four serious show-stoppers in OOo for the organizations where I have integrated OOo. I really thought that we would never see it fixed.

Eike, your work is very, very appreciated! Thank you!

This bug has been fixed by Eike Rathke in LibreOffice, the OOo fork:
https://bugs.freedesktop.org/show_bug.cgi?id=37978

Thank you for fixing this bug. I too have been waiting many years for this fix.