Issue 70003 - Support MS [$-F800], [$-F400] system date/time number format code modifiers
Summary: Support MS [$-F800], [$-F400] system date/time number format code modifiers
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: OOo 2.0.4
Hardware: All All
: P3 Trivial with 2 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: oooqa
Depends on: 51662
  Show dependency tree
Reported: 2006-09-30 14:54 UTC by andreschnabel
Modified: 2017-05-20 11:11 UTC (History)
2 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---

Screenshot of the wrong date and time format (OOo 2.2 RC4) (97.88 KB, image/png)
2007-03-24 23:27 UTC, maand
no flags Details
Screenshot of the wrong format of numbers (OOo 2.2 RC4) (109.09 KB, image/png)
2007-03-24 23:29 UTC, maand
no flags Details
Screenshot of the wrong format of date in Zh-cn (OOo 3.0_m23) (83.14 KB, image/jpeg)
2008-07-21 10:07 UTC, amy2008
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description andreschnabel 2006-09-30 14:54:11 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
You can use*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.
Comment 1 frank 2006-10-12 16:05:10 UTC
Hi Daniel,

please have a look at this one.

Comment 2 daniel.rentz 2006-11-23 09:52:47 UTC
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 
Comment 3 ooo 2006-11-23 11:59:20 UTC
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?
Comment 4 andreschnabel 2006-11-23 12:22:58 UTC
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.
Comment 5 maand 2007-03-24 23:27:25 UTC
Created attachment 43906 [details]
Screenshot of the wrong date and time format (OOo 2.2 RC4)
Comment 6 maand 2007-03-24 23:29:33 UTC
Created attachment 43907 [details]
Screenshot of the wrong format of numbers (OOo 2.2 RC4)
Comment 7 maand 2007-03-24 23:32:55 UTC
The Screenshots are taken of the output of the document from the weblink
(documentation of the actual status).
Comment 8 amy2008 2008-07-21 10:07:59 UTC
Created attachment 55250 [details]
Screenshot of the wrong format of date in Zh-cn (OOo 3.0_m23)
Comment 9 amy2008 2008-07-21 10:20:33 UTC
In Dev300_m23 on WinXP.
Found that date had the wrong format. Seen Attachment
Comment 10 thackert 2008-09-28 17:11:47 UTC
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 :)

P.S.: I know this issue is targeted "OOo later", but maybe it was fixed
inbetween ... ;)
Comment 11 ooo 2008-10-01 12:01:55 UTC
@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.
Comment 12 Marcus 2017-05-20 11:11:20 UTC
Reset assigne to the default "".