Bug 131524 - Date format in a cell with conditional formatting turns to money
Summary: Date format in a cell with conditional formatting turns to money
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) rc
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
Reported: 2020-03-24 08:39 UTC by Maciej
Modified: 2020-04-04 08:52 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

part of spreadsheet (4.82 KB, image/png)
2020-03-24 08:39 UTC, Maciej
spreadsheet for three months (90.14 KB, image/png)
2020-03-25 17:01 UTC, Maciej
spreadsheet for three months (20.86 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-03-25 17:01 UTC, Maciej

Note You need to log in before you can comment on or make changes to this bug.
Description Maciej 2020-03-24 08:39:17 UTC
Created attachment 158916 [details]
part of spreadsheet

In a cell where supposed be date when condition is applied immediately displays value as money. If condition is not applied date is as date format. See attached picture. It is my basic money calc. When expenses are all paid, cell color turns to green. Until then is clear. Unfortunately when color turns to green also turns date format into money format. Unable to change it in any way. Problem occured since Libre Office 6.3.3, Version 6.2.2 works just fine
Comment 1 Oliver Brinzing 2020-03-24 18:02:41 UTC
Thank you for reporting the bug. 

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.)

I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided.
Comment 2 Maciej 2020-03-25 17:01:04 UTC
Created attachment 159001 [details]
spreadsheet for three months

Now you can see how it works for three months. I have this file for almost 15 years, never before had problems like that.
Comment 3 Maciej 2020-03-25 17:01:49 UTC
Created attachment 159002 [details]
spreadsheet for three months

Here is part of spreadsheet for three months
Comment 4 Maciej 2020-03-25 17:02:47 UTC
As requested mor data to analyze.
Comment 5 Oliver Brinzing 2020-03-29 08:34:08 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

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

setting to "NEW" but I think, this issue is a duplicate of:
Bug 117715 - Settings from Conditional formatting should overwrite direct formatting

cause cell F3 has a hard cell number format: DD.MM.YY
and conditional format: 
apply cell style "zapłacone" if $I3=0
-> number format: [$£-809]#.##0,00;-[$£-809]#.##0,00
-> background: 	  green

with $I3=0
LO green background and DD.MM.YY
LO green background and £

> So the only solution for consistency seems to be to effectively 
> revert the fix for 93300 and have all conditional formatting take precedence. 
> This might be what the majority expects
Comment 6 Eike Rathke 2020-03-30 16:19:13 UTC
The style 'zapłacone' that is applied in the conditional format has the number format set to
so that is exactly what is applied. There's nothing wrong.
To apply a date format instead, create a style inherited from 'zapłacone' (to inherit for example the green background) and set the desired date format, then in the conditional format manager remove the column F ranges from the currently applied ranges for style 'zapłacone' and create a new conditional format with the same formula that applies the newly created style for dates to column F ranges.