Apache OpenOffice (AOO) Bugzilla – Issue 25468
Date Format translation problem from Excel to OOo
Last modified: 2013-08-07 15:15:02 UTC
There is a standard 'number' format for a date in a cell in Excel of "dd-mmm- yy", with the month in three letters. This is not one of the standard formats offered by OOo. In Excel, the "dd-mmm-yy" format is listed in the selection menu for 'Format- Cell-Number-Date'. The "dd-mmm-yy" format is not listed in this menu in OOo (SO6, SO7, OOo1.0.x, OOo1.1.0). The "dd-mmm-yy" format is imported from an Excel file as "d mmm yy" (which is a standard OOo date format), dropping the leading 0 from the day number and losing the hyphens. If the date format is set as "dd-mmm-yy" in an OOo document and exported to '.xls' file format, and then reopened with OOo, there is no problem, but if you open and save the file with Excel, and then reopen the file with OOo the cell format becomes "d mmm yy" in OOo, although it will remain "dd-mmm-yy" if the same file is opened in Excel. It's the import action in the OOo filter that causes the problem. The situation has been found with import of files edited with: Excel95 (W95), Excel97 (W95, W98), Excel2000 (W98SE, W2000, XP), ExcelXP (XP) [all UK]; Excel97 (W98SE), ExcelXP (XP) [Canada/US]. {NB: The Excel 97 has included both SR-1 and SR-2 versions.} and with import to: SO6 [with at various times, PP1, PP2 & PP3] (XP, W98, W98SE, Linux-SuSe8.0, Linux-SuSe8.2), SO7 (XP), OOo1.0.1 (Linux-Debian), OOo1.0.3 (Linux-Debian), OOo1.1.0 (XP, Linux-Debian, Linux-SuSe8.2). {The Debian Linux's are permanent installs from a Knoppix 3.1 CD, kernel 2.4.20.} Same date format import situation everywhere. Latest test procedure: Saved a spreadsheet from OOo1.1.0 (XP) to '.xls' file with number format in a date cell of "dd-mmm-yy". Reopen file in OOo - no change, cell displays as "dd-mmm-yy". Open file in OOo (Linux-Knoppix3.1) - no change, cell displays as "dd-mmm-yy". Open file in Excel 97 (SR-1) - no change, cell displays as "dd-mmm-yy". Save file under new name in Excel 97 (SR-1) - no change, cell displays as "dd- mmm-yy". Reopen both files in Excel 97 (SR-1) - no changes, both display as "dd-mmm-yy". Reopen both files in OOo (XP and Linux) - original file displays as "dd-mmm- yy", new file saved from Excel displays as "d mmm yy". What I think may be happening is that Excel saves the actual "dd-mmm-yy" format as a default code, and the filter translation into OOo outputs this default code as "d mmm yy".
Hi, this is a problem from Excel which has different default (standard) formats for different locales. As this is not incorporated into calcs import tables for numberformats, I flag this as enhancement. Daniel will comment on this Issue. Frank
Fixed in src680/dr19. Submitter mentiones UK and Canada locales. Added Excel default formats of both locales. Added support for the following locales: LANGUAGE_ENGLISH_UK LANGUAGE_ENGLISH_CAN (Canada) LANGUAGE_ENGLISH_AUS (Australia) LANGUAGE_ENGLISH_EIRE (Ireland) LANGUAGE_ENGLISH_NZ (New Zealand) LANGUAGE_ENGLISH_SAFRICA (South Africa) LANGUAGE_GERMAN_AUSTRIAN LANGUAGE_GERMAN_SWISS LANGUAGE_GERMAN_LUXEMBOURG LANGUAGE_GERMAN_LIECHTENSTEIN Completed/finished the following already supported locales: LANGUAGE_ENGLISH_US LANGUAGE_GERMAN (Germany) LANGUAGE_JAPANESE LANGUAGE_KOREAN LANGUAGE_CHINESE_SIMPLIFIED LANGUAGE_CHINESE_TRADITIONAL Still missing: All other locales: see issue 26092
Raised issue 26092 to target OOo 2.0 (Hebrew only). Remaining issue for other locales: issue 29949.
reopened
back to QA
fixed
verified in internal build cws_dr19
closed because fix available in OOo1.9m54