Issue 72229 - NF-DATE: Separate UI date format setting needed for Calc
Summary: NF-DATE: Separate UI date format setting needed for Calc
Alias: None
Product: Calc
Classification: Application
Component: formatting (show other issues)
Version: OOo 2.0.2
Hardware: All All
: P3 Trivial with 5 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
: 95101 (view as issue list)
Depends on:
Reported: 2006-12-03 21:27 UTC by blilly
Modified: 2017-05-20 11:30 UTC (History)
1 user (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description blilly 2006-12-03 21:27:59 UTC
In Calc, one can format column/cell date entries using Format -> Cells... -> 
Numbers -> Category = Date -> Format = 1999-12-31 to get ISO date formatting.
However, when selecting a cell, the "Input Line" display appears to be 
formatted according to the Tools -> Options -> Language Settings -> 
Languages -> Locale setting.  "Esperanto" matches ISO date format, but screws 
up other settings (e.g. decimal point/thousands separators).
There really should be separate settings for separate UI categories, rather
than being bundled under one poorly-named (date format has absolutely nothing 
to do with language) catch-all.

1. Open a new spreadsheet in Calc
2. Highlight a column heading, select Format -> Cells... -> Numbers -> Category 
= Date -> Format = 1999-12-31
3. enter a date, e.g. 2006-12-03
4. press Enter then navigate back to the cell
5. Observe that the "Input Line" display has a different format from that 
selected for the cell
6. Cuss loudly...

Should either format "Input Line" in accordance with selected cell or provide a 
way to select formatting of UI "Input Line" independently for different 
categories (dates, times, currencies, etc.)

I've marked this as "Enhancement", but "Input Line" formatting which conflicts 
with the user-specified cell formatting (and with no way to fix it without 
screwing up other things) is really a bug.
Comment 1 jlgarcia 2007-02-07 22:16:17 UTC
I have had a similar problem in Calc. Date input would be interpreted as
MM/DD/YY, while I actually use DD/MM/YY and I couldn't find how to change it.
The explanation in this issue open the "eyes" for a solution, but it is not a
clean one having to modify the locale in the Options... I would rather prefer a
specific option for the date input format.
Comment 2 oc 2008-07-15 10:43:56 UTC
reassigning features and enhancements to user which
will be the default owner for those tasks (was introduced some time ago)
Comment 3 tab 2008-07-16 04:17:30 UTC
Regardless of the user's favorite date format (2007-12-31, 12/31/07, 31.12.2007,
...), there should be ONE interface to enter or edit the date, such as 3 fields
named 'year', 'month', 'day', or a little calendar where one would pick the
date. Once entered, the date would be displayed in the user's format.
That would avoid having to alter the editing algorithm to suit the user's format.
Comment 4 corigo 2008-10-21 08:56:32 UTC
I'm not sure which of these issues to vote for, this one or 5556. On the one 
hand it seems logical, that if I set a date format I want to use that format, 
but I agree, this may not always be the case. Either way it is very clear that 
I do not want to rely on either my OS or my Language Setting to define my date 

I agree that the interface needs to allow for defining the default entry/edit 
format for dates independent from these other elements. 
Comment 5 njglin 2008-10-21 21:37:04 UTC
Displayed format should be always the same whether on input line, in the cell,
or in the cell when clicked for editing.  That is the most basic intention of
5556 (I opened that one).
Remaining info about locale etc. was provided in 5556 to provide context and
allow problem recreation.
I can see there would be users which would want to ALWAYS have the edit in their
preferred format - that would call for an option within OOO.
Comment 6 tab 2008-10-22 03:07:05 UTC
marcsinclair Sun Oct 19 2008 wrote:
'you CAN display dates as ISO. but when you try editing
them, and you are confronted by some unknown format.'
That's why I suggested on 2008 Jul.16: 'there should be ONE interface to enter
or edit the date, such as 3 fields named 'year', 'month', 'day', or a little
calendar where one would pick the date.
This format need not be to everyone's taste since it's only for editing. But it
must be clear.
Comment 7 corigo 2008-10-22 04:26:55 UTC
yeah, marc, I'm sorry but I don't like your idea at all. I especially don't 
want a pop-up calendar when I'm entering a date, nor 3 fields, as once again I 
am constrained to the order of those fields. Just want to say I will type my 
dates this way, regardless of OS or language settings and have OOo say "Ok" and 
always display dates in Edit mode as defined by my setting.
Comment 8 tab 2008-10-23 01:42:40 UTC
corigo: Marc did not suggest those 3 fields, I did. (I quoted him because his
comment support my suggestion)
Your demand to 'always display dates in Edit mode as defined by my setting' may
be unrealistic because editing (according to a user-defined format) is much more
demanding (from a programmer's stand point) than displaying. Besides, editing
with 3 separate, all-numeric fields is safer than editing a string like
'2008-02-10', where one could delete a dash, enter O instead of 0, or whatever.
A pop-up calendar is even safer --you cannot enter an invalid date, and you see
which day of the week it is even if your format hides it.
Comment 9 blilly 2008-10-23 11:24:47 UTC
Comments for tab:

1. I agree (with corigo) that either three fields for a single cell, or a 
pop-up is [highly] undesirable; I want to continue to be able to enter/edit 
values quickly and efficiently.  I can do so now using only the numeric 
keypad; I cannot with your proposals.

2. If you carefully reread the original description (which I wrote), you may 
notice that it says nothing about editing; it describes the display of dates 
on the "Input line" vs. the current cell.  Your comment "see which day of the 
week it is" should have immediately alerted you to yet another relevant 
problem with your proposal; viz. calendar display is yet another 
(non-language) user preference (some prefer weeks beginning on Monday, some on 
Sunday; and that's just for conventional Gregorian calendars -- other calendar 
types raise other issues (see and follow 
links for other types)).

3. Editing is not the topic of this issue; If I open a spreadsheet 
with "Language Settings" -> "Languages" -> "Language of" "Locale setting" set 
to "English (USA)" and date cell format set to ISO 8601 format (format code 
YYYY-MM-DD), a cell date for today is displayed in-cell as "2008-10-23", 
whereas in the UI "Input line" is is DISPLAYED as "10/23/2008".  That is the 
issue described here.

4. With settings as described immediately above, I can edit (separate topic) 
in the input line as "2009-11-24" or "November 24, 2009" or "11.24.2009" or 
many other formats, all describing a date one year, one month, and one day in 
the future.  In each case the input is correctly parsed and correctly 
displayed in the cell (but the "Input line" still displays in some other 
format as determined by "Language Settings" (date format isn't a Language 
setting) -> "Languages" (date format isn't a Language) -> "Language of" (date 
format isn't a language) "Locale setting" (<- there is the problem originally 
described, viz. bundling unrelated UI preferences in a catch-all category).  I 
have no complaint about entering data on the "Input line"; it works (please 
don't screw it up with pop-ups, etc.!).  The complaint -- to reiterate -- is 
that the display in the "Input line" box differs markedly from that selected 
for the cell and moreover, that the format used for that "Input line" display 
is not readily changed.

5. I'd be happy with the "Input line" display formatted per the cell's defined 
format, which would resolve the issue as far as I'm concerned.  Alternatively, 
providing a means of separate UI settings for separate preferences would be an 
improvement, with the caveat that the whole notion of a "locale" is 
fundamentally misguided (e.g. some accounting types like negative currency 
values displayed in parentheses; that is not a language-specific issue nor is 
it a geo-political issue -- it is merely a user preference).  I personally use 
a KDE desktop, which does provide separate preference settings for date, time, 
currency, etc., and I'd be happy if OOo would use those settings, but alas, 
OOo isn't a KDE application.  In any case, the problem is that the current 
scheme where it is a) difficult to find where the UI gets its date format 
settings (because it is listed under the irrelevant category of 
language,language,language,locale) and b) having found where it is configured, 
it is impossible to independently change the UI date format display.
Comment 10 tab 2008-10-25 04:38:39 UTC
1. 'enter/edit values quickly and efficiently.[..] I cannot with your proposals.'
Why not? You fill a field with the same numeric keypad; and you change field
with Tab or the arrow keys, which you need to jump over the dashes, dots or
slashes of a string. And, with fields, tabbing from the last field could loop
back to the first; string editing does not do that. And fields can be checked:
Month cannot exceed 12, Apr.31 does not exist, etc. (Even if you don't make dumb
mistakes, you could hit on key twice and enter 'Oct.177' as a string. The Day
field will reject 177.)
2. The calendar type could be an option --not (I agree with you) determined by
"Language Settings" or "Locale setting".
3. If all you want is display, why even look at the Input line? It's useful only
for displaying formulae (since cells show results), and for editing --That's why
it's called 'Input line'. I agree that cell and input-line formats should match,
but that's a minor problem --until editing is needed. OO has many other more
important bugs to fix!
Comment 11 Regina Henschel 2009-06-23 18:52:30 UTC
*** Issue 95101 has been marked as a duplicate of this issue. ***
Comment 12 ooo 2010-01-21 18:51:34 UTC
Grabbing issue.
Comment 13 Marcus 2017-05-20 11:30:58 UTC
Reset assigne to the default "".