Issue 5556

Summary: NF-DATE: Date format is not consistent, for ISO 8601 input a corresponding cell format should be applied and input line should match.
Product: Calc Reporter: njglin <njglin>
Component: formattingAssignee: ooo
Status: CONFIRMED --- QA Contact:
Severity: trivial    
Priority: P4 CC:, erikanderson3, issues, jbf.faure, marcsinclair, mdxonefour, nesshof, njglin, roger.myjunk, stipanovich
Version: OOo 1.0.0   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: FEATURE Latest Confirmation on: ---
Developer Difficulty: ---

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
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:
    - Your locale: English (United States)
    - Short date format: yyyy-MM-dd
    - Long date format: dddd, dd MMMM, yyyy (since yyyy-mm-dd, dddd is not 
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.
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 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

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

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

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.

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

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.  

Comment 23 erikanderson3 2009-05-31 06:42:50 UTC

> 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
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
Grabbing issue.
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, 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 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 

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.

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 

- 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 

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 

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 

(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: