Bug 130958 - Dates formatted incorrectly in 6.3
Summary: Dates formatted incorrectly in 6.3
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Keywords: bibisected, bisected
Depends on: 117715
Blocks: Conditional-Formatting
  Show dependency treegraph
Reported: 2020-02-26 13:01 UTC by Steve Boatman
Modified: 2022-03-08 09:13 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

I've redownloaded/installed V and the problem persists (26.44 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-02-26 18:30 UTC, Steve Boatman

Note You need to log in before you can comment on or make changes to this bug.
Description Steve Boatman 2020-02-26 13:01:06 UTC
Opening a document in 6.3 dates (should be MM/DD) are formatted as base numbers. Using the format/cells/numbers doesn't fix the problem and they continue to be rendered as raw numbers. Reverting to fixes it. This happens on both Mac and Widnows.

Steps to Reproduce:
1.open existing spreadsheet
2.dates formatted as MM/DD render as raw numbers 
3.Format/Cells/Numbers fails to render the cell correctly

Actual Results:
After formatting the number stays as a raw number and not a MM/DD date.

Expected Results:
The cell should be rendered as MM/DD

Reproducible: Always

User Profile Reset: No

Additional Info:
Format the cell as a date
Comment 1 Oliver Brinzing 2020-02-26 17:51:02 UTC
Thank you for reporting the bug. 

To be certain the reported issue is not
related to corruption in the user profile, could you please reset your
Libreoffice profile ( https://wiki.documentfoundation.org/UserProfile ) and

If the problem still persists, please attach a sample document, as this makes it easier for us to verify the bug. 
(Please note that the attachment will be public, remove any sensitive information before attaching it. 
See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)

Which LO version did you use?
Please copy information from Menu "Help/About LibreOffice"

I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided.
Comment 2 Steve Boatman 2020-02-26 18:30:06 UTC
Created attachment 158210 [details]
I've redownloaded/installed V and the problem persists

The problem is in cell E14 and probably others.
Comment 3 QA Administrators 2020-02-27 02:46:58 UTC Comment hidden (obsolete)
Comment 4 Oliver Brinzing 2020-02-29 09:34:30 UTC
reproducible with:

Version: (x64)
Build-ID: dd0751754f11728f69b42ee2af66670068624673
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win; 
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE

This looks like a problem with conditional formating, e.g. cell $2020.$E$14 

Formula is AND((MONTH($A$1)=3);NOT(ISNUMBER(E14)))
Conditional Style_7

shows "43908" instead of "3/18" 

but *not* reproducible with:

Version: (x64)
Build-ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win; 
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
Comment 5 Oliver Brinzing 2020-02-29 09:53:48 UTC
this seems to have started with:


commit 2b0626161d3ef7c4a51007018d13ec391d3a2b04	[log]
author	Eike Rathke <erack@redhat.com>	Sat Oct 26 23:00:20 2019 +0200
committerEike Rathke <erack@redhat.com>	Sun Oct 27 00:00:16 2019 +0200
tree c7db2342f6de8d38e966475f2f161f32894089e3
parent e5874d6a72629ae298c97172f3c06137ed3dcab0 [diff]

Resolves: tdf#117715 Conditional format takes precedence; reverts tdf#93300

$ git bisect bad 1f49104ec182201a960f7f8d869abfa55c4288e1 is the first bad commit
commit 1f49104ec182201a960f7f8d869abfa55c4288e1
Author: Norbert Thiebaud <nthiebaud@gmail.com>
Date:   Sat Oct 26 15:17:23 2019 -0700

    source 2b0626161d3ef7c4a51007018d13ec391d3a2b04
    source 2b0626161d3ef7c4a51007018d13ec391d3a2b04

:040000 040000 d6bd4e317c5002bf6cf21f12ddfdbb185ba23f41 58e155b4a156f609c7d95c77f2128514ca5c5ccc M      instdir

f0830413@LAPTOP-98M8UIU5 /cygdrive/d/sources/bibisect/bibisect-win64-6.4
$ git bisect log
# bad: [75af2782b7f006d1c31ad11e84d5ab6bd7f74ed0] source 20be5cd0bdc57d812bf34a2debfe48caa51de881
# good: [8d1eaf05d47fd1c56ddecbe57a9a7c8289ede7f4] source c98b1f1cd43b3e109bcaf6324ef2d1f449b34099
git bisect start 'master' 'oldest'
# good: [e13037966b62b9d258d5cf6d96586de5e2bafea8] source 4a0b2b8024fa6fb8a0ab3e474b7d64fc455028b5
git bisect good e13037966b62b9d258d5cf6d96586de5e2bafea8
# good: [7cba838374e1acd7b8a6e114d7b12bf6370cd7ab] source d6ea967e040d01ec69649ac689472018e477db34
git bisect good 7cba838374e1acd7b8a6e114d7b12bf6370cd7ab
# bad: [39293e77bf2d89be2f2f7afeb4c0c65f7231ff3a] source 9b400e84b1689997378f6738b97b71b09cdb7be6
git bisect bad 39293e77bf2d89be2f2f7afeb4c0c65f7231ff3a
# good: [f3a96af9745e81ff222b8ab4a0b52960678b55eb] source ad0a97ca200328d336a3cd58942f71a824b1f23a
git bisect good f3a96af9745e81ff222b8ab4a0b52960678b55eb
# bad: [2ec9f9a8ae1a57eac4d9f2f5e62a3067e27f533d] source 622465526cb0832145f2d0c1a2669f941b060273
git bisect bad 2ec9f9a8ae1a57eac4d9f2f5e62a3067e27f533d
# good: [519f0eb28d2cf9ac9f997a71d4e5fe2a78536f9e] source 0648aba5740a9ab62a98695a493ea9d1edbc7207
git bisect good 519f0eb28d2cf9ac9f997a71d4e5fe2a78536f9e
# good: [d4a20794de51a4571697af89189b0607be8905e8] source 706afd3e765e98489a2b43934a259626f9f0be01
git bisect good d4a20794de51a4571697af89189b0607be8905e8
# bad: [9f4ff14fd759f0427529c7bbf5827df512d19176] source 2d8efd1afe03a4009600e914910144b70982e98d
git bisect bad 9f4ff14fd759f0427529c7bbf5827df512d19176
# good: [46572ded289e955f42cac730ee883472c3bba8f3] source 6147549830669226ca333a55fcd05c51e21cf9a6
git bisect good 46572ded289e955f42cac730ee883472c3bba8f3
# bad: [d887ba63b0cf26c550c25faff0f06a74246a3d3a] source 2070290de6bd5c44c9786aac56aa4d3e2f3ec522
git bisect bad d887ba63b0cf26c550c25faff0f06a74246a3d3a
# good: [37287d9c4ff94c00ca71bcd49bb51f587a52c8e5] source e5874d6a72629ae298c97172f3c06137ed3dcab0
git bisect good 37287d9c4ff94c00ca71bcd49bb51f587a52c8e5
# bad: [40e724b53981f842561b924a6c18bf6ad6b99ede] source b7735d9c705bdec53ddb0077b93ae70ce4c2df48
git bisect bad 40e724b53981f842561b924a6c18bf6ad6b99ede
# bad: [1f49104ec182201a960f7f8d869abfa55c4288e1] source 2b0626161d3ef7c4a51007018d13ec391d3a2b04
git bisect bad 1f49104ec182201a960f7f8d869abfa55c4288e1
# first bad commit: [1f49104ec182201a960f7f8d869abfa55c4288e1] source 2b0626161d3ef7c4a51007018d13ec391d3a2b04
Comment 6 Eike Rathke 2020-03-02 23:06:40 UTC
And so this is not a regression, the commit for bug 117715 explicitly reverted the interim erroneous behaviour introduced with the fix for bug 93300 this document apparently relied on. The conditional format's number format again takes precedence over the hard attribute.

The problem here is that all three conditional styles that could match on cell '2020'.E14 (ConditionalStyle_2, ConditionalStyle_6, ConditionalStyle_7) override the number format with General instead of using the desired M/D format. For the current data constellation the condition's (Formula is B3>$A$1) ConditionalStyle_6 is applied.

Confusingly the preview in the conditional formatting dialog displays the underlying hard attribute's (M/D;@) number format output, which is wrong and misleading. This should be fixed.

One question: was the document originally created with Excel and was the behaviour there different? (the trailing ";@" text subformat part in M/D;@ is nothing a user would normally enter, hence the question..).
Comment 7 Steve Boatman 2020-03-02 23:21:32 UTC
The original spreadsheet was created in Libreoffice at least 5 or 6 versions ago. There is extensive conditional formatting based on the current date and the date keyed in as MM/DD. The spreadsheet worked well through all versions until
Comment 8 Steve Boatman 2020-03-05 01:25:44 UTC
There had been a quesiton regarding if the first verion of this spreadsheet had been created in Excel. I said no but after thinking about it more, it's possible. 

So, today I rebuilt everyting from scratch in Libreoffice After dealing with all the diffiulties of custom formatting I had everything working as espected. I then downloades Libreoffice for Windows and the date rendering problem is there again.
Comment 9 Steve Boatman 2020-03-06 18:27:21 UTC
I once again removed all conditional formatting and rebuilt the spreadsheet in Libreoffice This time I opened it in in Windows and everything seems to work correctly and the dates are formatted correctly.

So, my problem seems to be resolved although it still seems like a problem since the earlier spreadsheet worked in 6.2X and didn't in 6.3X.
Comment 10 QA Administrators 2022-03-07 03:29:25 UTC Comment hidden (obsolete)
Comment 11 Steve Boatman 2022-03-08 00:05:41 UTC
This  problem  no  longer exists with more  current  versions  of  Libreoffice.

I believe  the  original problem resulted from having created the original spreadsheet in MS Excel.