Bug 142216 - FILEOPEN XLSX Excel default time formats don’t match in Calc with Hungarian locale
Summary: FILEOPEN XLSX Excel default time formats don’t match in Calc with Hungarian l...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:7.2.0
Keywords:
Depends on:
Blocks: Number-Format
  Show dependency treegraph
 
Reported: 2021-05-11 12:15 UTC by NISZ LibreOffice Team
Modified: 2021-07-01 09:13 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Example file from Excel (11.03 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2021-05-11 12:15 UTC, NISZ LibreOffice Team
Details
Screenshot of the original document side by side in Excel and Calc (44.98 KB, image/png)
2021-05-11 12:15 UTC, NISZ LibreOffice Team
Details
Example file from Writer with all default LO-supported time formats as fields (10.11 KB, application/vnd.oasis.opendocument.text)
2021-05-11 12:16 UTC, NISZ LibreOffice Team
Details
The Writer reference file, just to avoid huge regressions (25.58 KB, image/png)
2021-05-11 12:16 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description NISZ LibreOffice Team 2021-05-11 12:15:27 UTC
Created attachment 171878 [details]
Example file from Excel

This is similar to bug #133672 but with Excel default time formats instead of dates.
Attached example file contains all default time formatting options applied on cells in a Hungarian Excel 2019.
Calc does not have the same set of defaults, so there are some conversion differences, especially with the locale-dependent format in A2.
Also the AM/PM translation does not match either.

Steps to reproduce:
    1. Open attached file in Calc

Actual results:
First item is padded to two digits.
Items with AM/PM are uppoercase instead of lowercase.
This may cause differences in display and autofilter results.

Expected results:
Ideally same display of time as Excel.

LibreOffice details:
Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: d7f734db2c078ced3ce08ad58cd816a79abe3bcf
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win
Locale: hu-HU (hu_HU); UI: en-US
Calc: CL
Comment 1 NISZ LibreOffice Team 2021-05-11 12:15:51 UTC
Created attachment 171879 [details]
Screenshot of the original document side by side in Excel and Calc
Comment 2 NISZ LibreOffice Team 2021-05-11 12:16:15 UTC
Created attachment 171880 [details]
Example file from Writer with all default LO-supported time formats as fields
Comment 3 NISZ LibreOffice Team 2021-05-11 12:16:47 UTC
Created attachment 171881 [details]
The Writer reference file, just to avoid huge regressions
Comment 4 Julien Nabet 2021-05-11 18:02:13 UTC
Code pointer I suppose:
i18npool/source/localedata/data/hu_HU.xml
50     <TimeAM>DE</TimeAM>
51     <TimePM>DU</TimePM>

282     <FormatElement msgid="TimeFormatskey2" default="true" type="medium" usage="TIME" formatindex="40">
283       <FormatCode>HH:MM:SS</FormatCode>
284     </FormatElement>

But I don't know if it can be changed only for xlsx/xls. Indeed, I can understand Excel can be seen as the reference for xlsx or xls but not for the other like ods files unless ods should also display the same?
Comment 5 Xisco Faulí 2021-05-31 17:17:14 UTC
@Eike, I thought you might be interested in this issue
Comment 6 Eike Rathke 2021-05-31 21:27:40 UTC
We can easily change the AM/PM representation from "DE" to "de." and "DU" to "du."

If the default time formats are to be expected as not displaying leading 0 we can of course also change those to H:MM and H:MM:SS

For both, AM/PM and time formats, the current definitions are from 2005 and thus probably never had a review or were corrected, if they should have been.

Of course, changing them will affect all documents, not just .xls/.xlxs, unless formats are explicitly assigned differently.

Note that the first time format in the attached .xlsx is stored as

    <numFmt numFmtId="165" formatCode="[$-F400]h:mm:ss\ AM/PM"/>

with the [$-F400] meaning display in whatever system default time format you have.
Comment 7 Eike Rathke 2021-06-04 11:42:21 UTC
There were no objections so I went ahead with
https://gerrit.libreoffice.org/c/core/+/116713
cast your veto there ;)

It was unclear to me whether the default date+time format should also be changed to display no leading 0, so from
YY-MM-DD HH:MM
to
YY-MM-DD H:MM
That can be added if wanted.
Comment 8 Eike Rathke 2021-06-07 11:59:24 UTC
No responses or objections => I'll merge that change as is.
Comment 9 Commit Notification 2021-06-07 12:00:31 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/8e7a9b0243a4a63841d1cb1faba118274d574053

Resolves: tdf#142216 Fix {hu-HU} AM/PM words, default time without leading 0

It will be available in 7.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 10 NISZ LibreOffice Team 2021-07-01 09:13:27 UTC
Verified in: 

Version: 7.3.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: 3964e0854507b2696ae0fbe48a4fb005c779947e
CPU threads: 4; OS: Windows 10.0 Build 17134; UI render: default; VCL: win
Locale: en-DK (hu_HU); UI: en-GB
Calc: threaded