Apache OpenOffice (AOO) Bugzilla – Issue 126977
Improper Date Formatting Using Custom Format Code yyyy"."m"."d" ("aaa")"
Last modified: 2016-05-19 23:20:10 UTC
Created attachment 85545 [details] Screenshot In an excel file for the date add the User Defined formatting as yyyy"."m"."d" ("aaa")" For a date like 01/01/2016 in the cell its actually shown as 2016.10.21 (Sat) whereas it should have been shown like 2016.01.01 (Sat). Attached is screenshot
Your screenshot show that you use LibreOffice. You are in wrong place here. This is a bug tracking system for OpenOffice.
This will probably case with even openOffice, I will verify and reopen if that's the case.
Created attachment 85548 [details] OpenOffice Screenshot
Attached is screenshot of openoffice Calc whihc too has same issue. Reopening the bug
I can see the problem using the code AAA or AAAA. But the help doesn't say anything about this codes and I don't know why it changes the visual representation of a date. Using YYYY". "M" ."D" ("NN")" or YYYY". "M" ."D" ("DDD")" like it is documented there isn't any problem.
The problem belongs only to special locale e. g. English (USA). Maybe only to English (USA)?
(In reply to mroe from comment #5) > I can see the problem using the code AAA or AAAA. But the help doesn't say > anything about this codes and I don't know why it changes the visual > representation of a date. Please note that the issue is with an Excel file. In Excel 2016, I created a cell with the format and entered 2016-01-01 (my default date format using Windows 10 Pro x64). The result was "2016.1.1 (Fri)". It happens that "aaa" for the three-letter day of week is not documented in the Excel Help either. On my system, if I enter 1/1/2016, that becomes a text cell. So on Excel, it seems important to use an entry that matches the specified format. (Or matches my OS-level default date notation. I use ISO dates on the system I used to check this.) When I open the .xls I saved it incorrectly shows the date as 2016.10.20 (Fri), suggesting an error in the origin date. When I enter 2016-05-19 in AOO 4.1.2 Calc, I get 2016.2.11 (Thu) which is of course very strange. However, if I save that modified file to .ods from Calc, and re-open it, the "2016.5.19 (Thu)" appears, as expected. If I check the formatting of that cell, what I get is Calc format code YYYY.M.D" ("[~jewish]NN")" which is rather remarkable. I notice that the other format examples show Jewish calendar examples too, such as Fri 22 Tevet 5760, which I must presume is ISO date 2016-05-19 :). What we know for certain is that (1) for the entry I made of 2016-05-19 the correct ordinal day number was stored in the cell, but the presentation of the formatted date was incorrect. On save and reloading, the presentation was correct. How Calc arrived at the Format code it did is inexplicable, even though it appears to work. The incorrect presentation on entry of a date is yet another feature of the problem.
(In reply to orcmid from comment #7) > (In reply to mroe from comment #5) > > I can see the problem using the code AAA or AAAA. But the help doesn't say > > anything about this codes and I don't know why it changes the visual > > representation of a date. > > Please note that the issue is with an Excel file. Yes, but the problem is completely independent from the loaded file. Have a look at http://www.ibm.com/support/knowledgecenter/SSKTWP_8.0.1/com.ibm.productivity.tools.help/text/common/01/05020301.html It seems AOO supports something like Lotus Notes. With the example date 10/07/2016 (really date not text) Language: Default - English (UK) it also works Format code: [~jewish] DD/MM/YYYY Preview: 04/04/5776 (is this right?) But I don't know if for | Arabic Islamic format → AAA or AAAA | the result in AOO is correct.