Issue 84414 - Formula bar: ability to set default date format on formula bar
Summary: Formula bar: ability to set default date format on formula bar
Alias: None
Product: Calc
Classification: Application
Component: editing (show other issues)
Version: OOo 2.3.1 RC1
Hardware: All Windows XP
: P3 Trivial with 6 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
: 85361 (view as issue list)
Depends on: 51662
  Show dependency tree
Reported: 2007-12-10 08:36 UTC by bijugc
Modified: 2013-09-09 10:29 UTC (History)
4 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

RegionalOptions.png (25.01 KB, image/png)
2007-12-10 08:37 UTC, bijugc
no flags Details
OOoCalcDate.png (31.96 KB, image/png)
2007-12-10 08:38 UTC, bijugc
no flags Details
excel.png (24.50 KB, image/png)
2007-12-10 08:38 UTC, bijugc
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description bijugc 2007-12-10 08:36:13 UTC
Sub: Formula bar: ability to set default date format on formula bar.

On my OS date format is yyyy-MM-dd
And the worksheet cell format is YYYY-MM-DD

Still OOo Calc show content on formula bar in an alien date format.
I also dont see any option in Tools > Options to set default date display format
for the whole sheet.
* I believe Lotus 123 use to have workbook date format setting.
* MS-Excel follow system date format.

1. Goto MS-Windows start menu
2. Goto Control Panel > Regional and Language Options
3. Select "Regional Options" tab
4. Select "Customize..." button (see attachment RegionalOptions.png)
5. Goto "Customize Regional Options" screen, then "Date" tab
5. Set Date format to yyyy-MM-dd
6. Click "OK" on "Customize Regional Options" 
7. click "Apply" on "Regional and Language Options" screen
8. Start OOo Calc
9. Select a Cell
10. from menu select Format > Cells..
11. on "Numbers" tab 
12. enter format code as YYYY-MM-DD
13. click "OK" to come back to the data entry mode
14. enter "2007-11-30" 
15. Press Enter Key
16. up arrow to select date again 
17. on formula bar you see 11/30/2007  (see attachement OOoCalcDate.png)

Expected date format "2007-11-30" 
as in MS-Excel (see excel.png)

Issue 30216 for Writer says there is a way to set default date format (may not
for formula bar) but I dont see it in OOo Calc 2.3.1
I would have expected it in menu under "General" settings
... > Tools 
... ... > Options ... 
... ... ... >
... ... ... ... > General            <---- here
... ... ... > Calc
... ... ... ... > General            <---- or here

I can select entire sheet and select Format > cells to choose format as
YYYY-MM-DD, but it change all numeric cell including percent and dollor cells to
date format. Which is not as I expected.
Comment 1 bijugc 2007-12-10 08:37:30 UTC
Created attachment 50198 [details]
Comment 2 bijugc 2007-12-10 08:38:21 UTC
Created attachment 50199 [details]
Comment 3 bijugc 2007-12-10 08:38:58 UTC
Created attachment 50200 [details]
Comment 4 kpalagin 2008-01-09 13:45:11 UTC
I think this issue in essence is duplicate to

*** This issue has been marked as a duplicate of 51662 ***
Comment 5 kpalagin 2008-01-09 13:45:51 UTC

Please vote for 51662.
Comment 6 bijugc 2008-01-10 02:35:40 UTC
Fix for issue 51662 may fix this.
But 51662 is only talking about the decimals

As issue 50670, issue 39898, issue 38494, issue 31663, issue 45312, issue 1820
are similar and some of them are fixed, but it did not automatically fix issue
51662. Hence I am little skeptical about this getting fixed automatically when
51662 get fixed.

So I am reopening this and adding this as depended to 51662, we should only
close this after testing and find it got fixed. 
Comment 7 Dotan Cohen 2008-01-20 18:04:55 UTC
I agree that this issue needs to be tested after 51662 gets fixed.

I am having similar problems on an Ubuntu machine running KDE. Calc does not
respect the locale's LC_TIME setting for date formatting in the edit bar. Dates
are always formatted MM/DD/YYYY regardless of locale.

In this example, I use locale "en_DK.utf8" which has the date format YYYY-MM-DD.

$ locale
$ openoffice.org2.3 -calc

Now, I select column A -> Format Cells -> Numbers -> Category -> Date -> Format
-> 1999-12-31. I now enter "2008-01-16" into cell A1 and hit enter. The cursor
moves to cell A2 and I see "2008-01-16" in cell A1. So far so good. Now, I click
on cell A1. The cell still says "2008-01-16" however the input line reads

The edit bar should format dates as they are formatted for display in the cell.
Failing that, it should display in the locale's preferred format.

Note: the problem _may_ be that Gnome's date format is not configured properly.
I use KDE on Ubuntu, with Gnome installed as the default desktop. However, due
to a poorly written ATI driver I cannot open Gnome and change the setting.
Googling and locating has not led me to discover which config file I should edit
in VI to change this setting. In any case, Gnome's setting should _not_ be the
deciding factor in which date format to use. Not everyone uses Gnome, and that
is specifically what LC_TIME is for.
Comment 8 kpalagin 2008-01-20 20:30:51 UTC
*** Issue 85361 has been marked as a duplicate of this issue. ***
Comment 9 Regina Henschel 2009-06-23 18:47:59 UTC
Please have a look at issue 5556 and issue 72229. You should move with your
votes to one of them.

I think this issue is duplicate to issue 5556 and it should be closed therefore.
Comment 10 Dotan Cohen 2009-06-23 20:52:59 UTC
Actually, as this issue is requesting a separately configurable date format for
the formula bar, I think it is a dupe of bug 72229. Bug 5556 requests that the
formula bar use the same date format as the cell is configured for. At least,
that is the difference that I perceive between the two bugs.
Comment 11 matteosistisette 2010-10-28 12:08:45 UTC

I opened one of those XLS files with dates. I MANUALLY selected all cells
contained dates and did "format cells" and selected "date" and "31/12/2010".

Then I entered some more data into the document and the disastrous spontaneous
change didn't happen, even after scrolling, so the workaround seemed to have worked.

Then as soon as I SAVED the document, all dates were changed to numbers.
There's NO WAY to keep updating an old XLS file generated with a previous version.

This makes OpenOffice Calc unusable. I'll look for another spreadsheet program.
Comment 12 matteosistisette 2010-10-28 12:11:01 UTC

Please delete my previous comment. I thought I was editing another issue, not
this one. I posted it here by mistake.

(Shame that one cannot edit or delete his/her own comments btw)
Comment 13 Edwin Sharp 2013-09-09 10:29:09 UTC
cell = 2007-11-30
bar = 11/30/2007

Rev. 1520602 Win 7