Apache OpenOffice (AOO) Bugzilla – Issue 70003
Support MS [$-F800], [$-F400] system date/time number format code modifiers
Last modified: 2017-05-20 11:11:20 UTC
Given is a excel file with some date / time values. Values have the systems default formatting (long date, short date, time). Calc fails to import this "system default" but uses the (us-english) fallback instead. You can use http://de.openoffice.org/source/browse/*checkout*/de/qa/testfiles/calc/calc-1-109242.xls as example (see table "Datums- und ZEitformate") The cause is, that Excel uses an (undocumented) escape sequence for "system default". This is [$-F800] for date, long system default [$-F400] for time, system default seems like some sort of bitvector, but I could not find a documentation. This does not only affect binary filters but Office 2003 XML and Office Open XML as well.
Hi Daniel, please have a look at this one. Frank
DR->ER: Number formats themselves are imported correctly. The cell 'Datums- Zeitformate'.A5 contains long date format with leading region code (or so): "[$-F800]TTTT\, MMMM TT\, JJJJ", which results in "TTTT\, TT\. MMMM JJJJJ" on German systems. Cell A6 contains time format "[$-F400]H:MM:SS AM/PM", which results in "HH:MM:SS".
Unimplemented feature/enhancement, not a 2.x target. Related to issue 23416. André, did Excel produce those F800/F400 modifiers arbitrarily by itself and if so which version and under which circumstances, simply by choosing a "system default" format, or how did they get there?
er: I'm using Excel2003 on Windows XP. System Locale is set to German (Switzerland) You need to format the cells exlicitly with the System default time format (just typing the time value will apply a custom format without the information that this is the system defauld). To get this in Excel - got to Format - Cells - Choose Time as number format Category - use the type with the asterisk (*) once this number format has been applied, you see the leading code if you go o the Format - Cells dialog again and select custom format category.
Created attachment 43906 [details] Screenshot of the wrong date and time format (OOo 2.2 RC4)
Created attachment 43907 [details] Screenshot of the wrong format of numbers (OOo 2.2 RC4)
The Screenshots are taken of the output of the document from the weblink (documentation of the actual status).
Created attachment 55250 [details] Screenshot of the wrong format of date in Zh-cn (OOo 3.0_m23)
In Dev300_m23 on WinXP. Found that date had the wrong format. Seen Attachment (http://www.openoffice.org/nonav/issues/showattachment.cgi/55250/70003%20calc% 20excel.odt).
Hello andreschnabel, *, I have tested the file with 3.0 RC2 under Debian SID/Experimental AMD64. I cannot confirm the wrong date format here, so it seems to be fixed. Could you or someone else can test it with a different OS/architecture, please? @andreasma: Just out of interest: Why did you attach the screenshot with the number format? Was there something wrong, too? I do not see anything wrong there, but maybe I am wrong ... ;) @amy2008: Could you attach a screenshot from M$O as well, so we see how it should look like? And please remove the spaces in the filename the next time :) HTH Thomas. P.S.: I know this issue is targeted "OOo later", but maybe it was fixed inbetween ... ;)
@thackert: This is not fixed, the specific behavior simply is not implemented at all. It may appear behaving correctly on your system if your system's Regional Settings have the same date/time formats as the default formats of your locale respectively the locale used in the number format. Which is likely on many systems, but not if the user modified the Regional Settings' formats.
Reset assigne to the default "issues@openoffice.apache.org".