Description: Found an issue with a spreadsheet created and saved as an .xlsx when viewed on an iOS device (iPad/iPhone) using the device’s default viewer. The issue is that the day of the month value in a date is displayed as the number of days since the beginning of the year. For example, February 4 is displayed as 2/35/19 instead of 2/4/19. I believe this is a bug in Calc as an .xlsx spreadsheet created in Microsoft Excel with the same dates displays correctly on an iOS device (iPad/iPhone) with the device’s default viewer. This issue has been observed on multiple iPads and iPhones. However, the date is displayed correctly in Calc, Excel and an Android’s default spreadsheet viewer. Attached to this bug are four files. Two files are similar test case .xlsx spreadsheets – one created using Calc and the other using Microsoft Excel 2013. The other two files are screenshots of those two .xlsx spreadsheets when viewed on an iPad with iOS 12.3.1. The test case Calc spreadsheet was created using Version: 6.1.5.2 Build ID: 10(Build:2) CPU threads: 4; OS: Linux 4.12; UI render: default; VCL: gtk3_kde5; Locale: en-US (en_US.UTF-8); Calc: group threaded However, I saw this same issue with .xls and .xlsx spreadsheets creating using a 5.x version of Calc on Windows 10 as well as the current version on Windows 10. Steps to Reproduce: 1. View attached file Calc-Created-Spreadsheet.xls on a iOS device using default speadsheet viewer Actual Results: 1. The spreadsheet will have the the day of the month value in a date is displayed as the number of days since the beginning of the year. Expected Results: 1. The spreadsheet should have the the day of the month value in a date is displayed as the number of days since the beginning of the month and not the beginning of the year. Reproducible: Always User Profile Reset: No Additional Info:
Created attachment 153232 [details] Calc Created Spreadsheet
Created attachment 153233 [details] Excel Created Spreadsheet
Created attachment 153234 [details] Screenshot of Calc Created Spreadsheet On iOS Device
Created attachment 153235 [details] Screenshot of Excel Created Spreadsheet On iOS Device
I found a similar bug report from the 2012-2013 time frame but that issue was fixed in LibreOffice 4.0.3. Bug 56873 - FORMATTING DATE: when saved as xls-file viewing on iPad/iPhone gives wrong format
The "reason" assumed in bug 56873 why it should be fixed is completely wrong and unrelated though, I guess no one reproduced it since then. Anyway, at least we do have some OOXML sample files here at this bug (thanks!). Though clearly if MS-Office displays the content correctly then the failure is with the iOS viewer, specifically if it displays month and year correctly and only fails on the day of month. Whatever, the formats and their assignments are stored slightly different by Calc and Excel, maybe someone with such iOS viewer could investigate what exactly makes it fail.
OK, I've got to the bottom of this. It was made easier by the same problem occuring in the OSX Quicklook viewer. OSX and iOS seem to interpret "DD" or "D" in a format specifier as "days since beginning of year" and "dd" or "d" as "days since beginning of month" Excel defaults to outputting its date formats in lower case, whereas LibreOffice defaults to upper case. By unzipping "Calc-Created-Spreadsheet.xlsx", changing the date formats in "xl/styles.xml" to lower case, and rezipping, I was able to fix the problem. Can anyone think of a reason why defaulting to lower case would be a problem? Entering date formats into Calc in lower case works fine, but it automatically converts them to upper case
Created attachment 153383 [details] Calc-Created-Spreadsheet.xlsx but with the date format specifiers changed to lower case
I guess that's some invention of those OSX/iOS viewers, there's no definition that uppercase DD or D would be format code keywords for day of year, see also https://support.office.com/en-us/article/Review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5 The ECMA-376-1:2016 OOXML specification doesn't say anything about case sensitivity in 18.8.30 numFmt (Number Format) or 18.8.31 numFmts (Number Formats) but gives codes in lowercase. There is no DD or D with a special day of year meaning. Historically LibreOffice internally uses all uppercase format code keywords and different casing doesn't produce duplicated format codes. AFAIK also in Excel one can enter codes in lower or upper case, just that Excel internally uses all lowercase format code keywords. So far this hasn't been a problem until this odd iOS/OSX thing. We maybe could convert format code keywords (and only those) to lowercase when writing MS (Excel) file formats.
That would be great. I'll also report it to Apple, but if Excel and the file specs refer to lower case characters, they may not consider it a bug
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/bd8b2a95e2c88cdab80c514c0d6364bad91d9177 Resolves: tdf#126773 Excel uses lowercase date and time format code keywords It will be available in 6.4.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.
I can confirm this is fixed in today's daily build. Thanks! Version: 6.4.0.0.alpha1+ Build ID: 25c390e17a7f1c018b5eed1ef7dfd568b76f4a84 CPU threads: 4; OS: Mac OS X 10.14.6; UI render: default; VCL: osx; Locale: en-GB (en_GB.UTF-8); UI-Language: en-US Calc: threaded