Issue 121151 - Uppercase MM in number format from Excel is not recognized
Summary: Uppercase MM in number format from Excel is not recognized
Alias: None
Product: Calc
Classification: Application
Component: formatting (show other issues)
Version: 3.4.1
Hardware: All All
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2012-10-03 10:38 UTC by Alessio Pollero
Modified: 2012-10-06 22:29 UTC (History)
2 users (show)

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

Example Report (8.97 KB, application/
2012-10-03 10:38 UTC, Alessio Pollero
no flags Details
Gnumeric screenshot (66.54 KB, image/png)
2012-10-03 14:24 UTC, Alessio Pollero
no flags Details
Report displayed in Excel 2010 (155.06 KB, image/png)
2012-10-03 14:31 UTC, Alessio Pollero
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description Alessio Pollero 2012-10-03 10:38:56 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 ...
Comment 1 Andrea Pescetti 2012-10-03 12:48:03 UTC
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?
Comment 2 Alessio Pollero 2012-10-03 14:24:11 UTC
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 .
Comment 3 Alessio Pollero 2012-10-03 14:31:54 UTC
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 .
Comment 4 Andrea Pescetti 2012-10-06 13:12:36 UTC
For reference, this is in LibreOffice (untouched there so far).
Comment 5 Andrea Pescetti 2012-10-06 20:54:35 UTC
So the issue here is that the number format
is not recognized completely.

Its meaning is:
- "[$-410]": (irrelevant in our case) interpret according to the Italian "culture", see
- "dd": show the day as 01-31, see
- "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
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.
Comment 6 Alessio Pollero 2012-10-06 22:29:01 UTC
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 ...