Bug 101147 - Format HH:MM:SS MM/DD incorrectly displayed
Summary: Format HH:MM:SS MM/DD incorrectly displayed
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.1.4.2 release
Hardware: All All
: high normal
Assignee: Laurent BP
URL:
Whiteboard: target:5.3.0 target:5.2.1 target:5.1.6
Keywords: regression
Depends on:
Blocks:
 
Reported: 2016-07-27 07:19 UTC by Laurent BP
Modified: 2016-08-09 19:38 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Example of date time format (7.41 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-07-27 07:19 UTC, Laurent BP
Details
Build all date-time formats and compare to XL representation (2.05 MB, application/vnd.oasis.opendocument.spreadsheet)
2016-07-27 07:55 UTC, Laurent BP
Details
Build all date-time formats and compare to XL and LibO 5.0 representations (2.30 MB, application/vnd.oasis.opendocument.spreadsheet)
2016-08-03 10:12 UTC, Laurent BP
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Laurent BP 2016-07-27 07:19:10 UTC
Created attachment 126426 [details]
Example of date time format

Description: bug 95339 ask to detect SS-MM as second-minutes. Its resolution in LibO 5.1.4.2 introduced an (other) regression for format like HH:MM:SS MM/DD

Steps to reproduce:
1. Format > Cells > Numbers, enter user format
HH:MM:SS MM/DD

Actual behavior:
both MM are treated as minutes

Expected behavior:
Second MM should be treated as month
Comment 1 Laurent BP 2016-07-27 07:55:33 UTC
Created attachment 126427 [details]
Build all date-time formats and compare to XL representation

To improve minute/month detection and avoid regressions, I built this huge spreadsheet which build all possible formats containing at least once MM, and compare its representation with XL. Up to 6 members in format.
Differences are count in first sheet.
To use it, you may need to change AA- and JJ- in YY- and DD- because I built it with French locale (sorry for this inconvenience).
Small numbers show few differences with XL.
bHaveMinute column is for the patch under review:
https://gerrit.libreoffice.org/27560/

*Discussion about remaining differences with XL*
Remaining cases which have different representation between XL and LibO concerns unconventional formats which have a second MM. It seems that XL forces second MM as being minutes, even if it is surrounded by date formats. Like in format
MM-HH-YY-MM-DD
LibO interprets both MM as month, while XL interprets second MM as minute. I don't think we should follow this interpretation.

/!\ File is quite long to open
Comment 2 Eike Rathke 2016-07-27 10:26:58 UTC
(In reply to Laurent BP from comment #1)
> It seems that XL
> forces second MM as being minutes
Not always. For example, in MM-MM or MM-YY-MM or MM-DD-MM it doesn't. The rule seems to be, if there was a preceding HH somewhere then the next MM will be minute. This is repeatable, e.g. in HH:MM:HH MM/DD/YYYY or HH:MM:HH YYYY-MM-DD both MM are minute. It also matches the behavior of MM-HH-YY-MM-DD below.

> even if it is surrounded by date formats.
> Like in format
> MM-HH-YY-MM-DD
> LibO interprets both MM as month, while XL interprets second MM as minute. I
> don't think we should follow this interpretation.
I think we should, for two reasons, one of course to be compatible, the second that otherwise we likely end up with yet some more special cases where we'd have to treat it differently from the "preceded by HH somewhere or immediately followed by SS" rule.
Comment 3 Commit Notification 2016-08-01 08:59:35 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

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

tdf#101147 Improve (again) minute/month detection

It will be available in 5.3.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 4 Commit Notification 2016-08-01 11:05:10 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4409cd87632712f71cb8cc85f63a5abd4a5dd117&h=libreoffice-5-2

tdf#101147 Improve (again) minute/month detection

It will be available in 5.2.1.

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 5 Commit Notification 2016-08-01 13:12:10 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=24a0aa8d9c28624b4e0961726922e054554af705&h=libreoffice-5-1

tdf#101147 Improve (again) minute/month detection

It will be available in 5.1.6.

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 2016-08-02 21:53:37 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=88134dcba680418496f7a1c70a47fde0159ce390

break the Excel rule for YMD and DMY, tdf#101147 follow-up

It will be available in 5.3.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 7 Laurent BP 2016-08-03 10:09:23 UTC
Some other formats are no more interpreted as they were:
HH DD/MM

I propose a new approach
https://gerrit.libreoffice.org/27823/
Comment 8 Laurent BP 2016-08-03 10:12:04 UTC
Created attachment 126541 [details]
Build all date-time formats and compare to XL and LibO 5.0 representations

This new version of test file add comparison with LibO 5.0 to detect regressions
Comment 9 Eike Rathke 2016-08-04 21:34:12 UTC
Note: due to locale dependent format code keywords the test document works only in a French fr-FR locale.
Comment 10 Commit Notification 2016-08-04 22:26:47 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0b168901ed5e767651262686af65994029b3039b&h=libreoffice-5-2

break the Excel rule for YMD and DMY, tdf#101147 follow-up

It will be available in 5.2.1.

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 11 Commit Notification 2016-08-04 22:26:52 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=97c0d2cf817d0d995dbc3d0901dbbfb4491e9664&h=libreoffice-5-1

break the Excel rule for YMD and DMY, tdf#101147 follow-up

It will be available in 5.1.6.

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 12 Commit Notification 2016-08-04 23:38:31 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=85badce0b8dce3c1c1cc4b7af2d71ec2ff8da4ef&h=libreoffice-5-2

tdf#101147 New approach to fix minute/month detection

It will be available in 5.2.1.

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 13 Commit Notification 2016-08-05 00:07:08 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=21639ac7f5b075808925186cdd5a102eb8d7a02a&h=libreoffice-5-1

tdf#101147 New approach to fix minute/month detection

It will be available in 5.1.6.

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 14 Commit Notification 2016-08-05 00:08:36 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=39e300612c97b7742c8d8417d4dc6c0022cfa040

tdf#101147 New approach to fix minute/month detection

It will be available in 5.3.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 15 Commit Notification 2016-08-09 19:38:32 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=284c8ce11957f11693e6b5026adc1a564d35e7e9

tdf#101096 tdf#101147 Add qa tests for minute/month detection

It will be available in 5.3.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.