Apache OpenOffice (AOO) Bugzilla – Issue 121151
Uppercase MM in number format from Excel is not recognized
Last modified: 2012-10-06 22:29:01 UTC
Created attachment 79716 [details] Example Report Opening the example report in the attachment calc doesn't show the month in the date cell, the value of the date is : 03/10/2012 but calc displays 03//2012 . Why ? Using MS Office or Gnumeric i don't have any problem and the date cell is displayed correctly, but with openoffice calc and libreoffice calc it is display without the month number ...
It seems that the problem is in the cell format. Use Format -> Cell (Italian version: Formato -> Celle) then "Date" and you will see that OpenOffice finds a "custom format" of "GG//AAAA"; set it to the standard "31/12/1999" format to fix it. I'm keeping this bug open, though, because it might be that we import your date format incorrectly. Could you provide screenshots of the date format as it appears in Excel and Gnumeric? Or just a code like "GG//AAAA" taken from the respective cell format settings?
Created attachment 79718 [details] Gnumeric screenshot As you can see the two dates are displayed correctly on gnumeric. The format code of the cell in gnumeric : [$-410]dd/MM/yyyy .
Created attachment 79719 [details] Report displayed in Excel 2010 The same report displayed in Excel, as you can see it is displayed correctly, i don't think that is a problem of the xls, rather I think it is a Calc issue .
For reference, this is https://bugs.freedesktop.org/show_bug.cgi?id=55572 in LibreOffice (untouched there so far).
So the issue here is that the number format [$-410]dd/MM/yyyy is not recognized completely. Its meaning is: - "[$-410]": (irrelevant in our case) interpret according to the Italian "culture", see http://msdn.microsoft.com/en-us/library/windows/desktop/dd318693%28v=vs.85%29.aspx - "dd": show the day as 01-31, see http://office.microsoft.com/en-us/excel-help/number-format-codes-HP005198679.aspx - "MM": not standard, it should be "mm" (lowercase) according to the same link and it would mean month as 01-12 - "yyyy": year, 4 digits (same link) So it seems that the report generation is indeed producing something that does not adhere to the Microsoft specification, since it produces "MM" instead of "mm". If you open your file in Notepad (yes, it's a normal text file) and replace [$-410]dd/MM/yyyy with [$-410]dd/mm/yyyy then OpenOffice will display it correctly. Otherwise, it will ignore the uppercase "MM" since it's invalid, and it will display day and year only. And now the problem is: do we want to relax the OpenOffice parser so that "MM" and "mm" are considered equivalent like apparently Excel and Gnumeric do? This usually requires discussion and valid arguments, unless you manage to find some Microsoft specification saying explicitly that we should be case-insensitive here.
I think that if MS Excel interpret the uppercase MM in the same way as the lowercase, OpenOffice should do the same to be adherent with the behaviour of MS Office at least with xls files ...