Bug Hunting Session
Bug 102075 - FILEOPEN: XLSX - Date shown as "[$-F400]H:MM:SS AM/PM" instead of according to locale
Summary: FILEOPEN: XLSX - Date shown as "[$-F400]H:MM:SS AM/PM" instead of according t...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
URL:
Whiteboard: target:6.0.0
Keywords: filter:xlsx
Depends on:
Blocks: Number-Format XLSX
  Show dependency treegraph
 
Reported: 2016-09-12 13:38 UTC by Tobias Burnus
Modified: 2017-10-05 07:32 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Excel XLSM file (XLS with macros) (122.09 KB, application/vnd.ms-excel.sheet.macroEnabled.12)
2016-09-12 13:38 UTC, Tobias Burnus
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Tobias Burnus 2016-09-12 13:38:04 UTC
Created attachment 127275 [details]
Excel XLSM file (XLS with macros)

Opens the file, goes to worksheet "Zeiten" (should be shown by default)

Result with LO 5.2.1.2:
* For the time values, "####" is shown; making the cell wider, it shows, e.g., "2:12:09 nachm." - The locale field shows "[$-F400]H:MM:SS AM/PM"

Expected as with MSO Excel 2007 on the same system:
* The time is shown as "14:12:09" as in Excel
[Default time value; the Excel "Format cell" dialog shows the note: "Time formats that begin with an asterisk (*) resopnd to changes in regional date and time settings that are specified for the operating system" where Type is "*13:30:55" - and in this dialog the default: "Locale (location): English (United States)" [cf. below.])


That's under Window 7 with the English version of MSO, the English interface of LO and Windows locale settings:
| Format: English (United States)
|   Short time: H:mm (! - note: default for the US is: H:mm tt)
|   Long time: H:mm:ss (!)

The document's language is (effectively at least, I don't know what MSO wrote to the file) German.
Comment 1 Buovjaga 2016-10-16 10:39:56 UTC
Confirmed.

Win 8.1 32-bit
MSO 2013
LibO Version: 5.3.0.0.alpha0+
Build ID: 970a66f8c919ea0524f216f40d21b3e2a8c88ccc
CPU Threads: 4; OS Version: Windows 6.2; UI Render: default; 
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2016-10-16_00:03:17
Locale: fi-FI (fi_FI); Calc: group
Comment 2 Eike Rathke 2017-09-17 18:08:46 UTC
Need to implement the [$-400] ("process or user default") and [$-800] ("system default") locale modifiers in some sensible manner. The given format code is to be ignored and the actual "User defined Regional Settings" (even if they don't exist (yet)) or "System" defaults are to be taken.
Comment 3 Eike Rathke 2017-10-04 17:53:44 UTC
Actually it's [$-F400] for time format as defined for the current system locale, and [$-F800] for long date format as defined for the current system locale. It seems the rest of the format code is entirely ignored in these two cases.

There appears to be no documentation other than in ECMA-376:2016 18.8.31 numFmts (Number Formats) at the very end of the last table (page 1791/1792), "Special language info values:" "0xf800: System long date format" and "0xf400: System time format". (Note that does not define <Currency String> being optional, but in fact it is.)
Comment 5 Commit Notification 2017-10-04 20:51:49 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7901ede8a63f41aea707bd19c22f834253161378

Add 0xF400 and 0xF800 constants for number format code modifiers, tdf#102075

It will be available in 6.0.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 6 Commit Notification 2017-10-05 07:27:19 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5be8c9cc1b92101e6f9fe5685df86e77d3eee3cc

Resolves: tdf#102075 support system [$-F400] time and [$-F800] long date

It will be available in 6.0.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.