Apache OpenOffice (AOO) Bugzilla – Issue 84414
Formula bar: ability to set default date format on formula bar
Last modified: 2013-09-09 10:29:09 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. Steps:- 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) BTW: 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 ... ... ... ... > Openoffice.org ... ... ... ... > General <---- here ... ... ... > Openoffice.org 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.
Created attachment 50198 [details] RegionalOptions.png
Created attachment 50199 [details] OOoCalcDate.png
Created attachment 50200 [details] excel.png
I think this issue in essence is duplicate to http://qa.openoffice.org/issues/show_bug.cgi?id=51662. *** This issue has been marked as a duplicate of 51662 ***
Closing. Please vote for 51662.
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.
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 LANG=en_DK.utf8 LANGUAGE=en_DK.utf8 LC_CTYPE="en_DK.utf8" LC_NUMERIC="en_DK.utf8" LC_TIME="en_DK.utf8" LC_COLLATE="en_DK.utf8" LC_MONETARY="en_DK.utf8" LC_MESSAGES="en_DK.utf8" LC_PAPER="en_DK.utf8" LC_NAME="en_DK.utf8" LC_ADDRESS="en_DK.utf8" LC_TELEPHONE="en_DK.utf8" LC_MEASUREMENT="en_DK.utf8" LC_IDENTIFICATION="en_DK.utf8" LC_ALL=en_DK.utf8 $ 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 "01/16/2008". 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.
*** Issue 85361 has been marked as a duplicate of this issue. ***
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.
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.
EVEN WORSE: 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.
** SORRY I DIDN'T MEAN TO POST IT HERE ** 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)
cell = 2007-11-30 bar = 11/30/2007 Rev. 1520602 Win 7