Created attachment 163714 [details]
spreadsheet with conditional format bug

This spreadsheet exhibits the behaviour where the direct format (yyyy-mm-dd) is removed when the conditional format is applied (cell >= 0, background, negative_warning),

Because you need to set up the date format for numbers to the style used by the condition.

it seems to happen only with 'negative_warning' Conditional formatting. Changing it to 'Warning' doesn't change the value

Reproduced in

Version: 7.1.0.0.alpha0+
Build ID: 58937aa4a50ecd681382f03331340da4c843b01e
CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

and

Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)

Right @xisco, modifying 'negative_warning' style in the tab 'numbers' to standard, seems to solve the issue. So in this way it can inherit the date format.

I think not a bug.

Excuse me, but the example problem I submitted uses neither negative_warning nor is a date format.  

These formatting problems did not appear in this spreadsheet, which has been in use here for years, until very recently. Unfortunately, I cannot successfully return to an earlier version of LO because of the way dependencies work on FreeBSD.  So I cannot determine at which point the problem first evidenced itself.

I have also been able to copy and paste cells exhibiting the problem from one spreadsheet to a new one and some, but not all, of the conditional formatting issues are cured thereby.  This does strike me as expected or desirable behaviour.

Likely related when I attempt to modify an existing style, say nagative_warning, I do not get a diagoue box to perform the modification.  I do get a dialogue box if I attempt to add a new style.

Created attachment 163941 [details]
Sample file modified.

Menu/View/Sidebar, top-right click and select styles.
Right-click on the style and select Modify, on tab 'Numbers' click on Standard.

The trick is that when I open the style sidebar and I select negative_warning and I press Modify nothing happens.  If I press New then a dialogue box opens with the  custom style inheriting from negative_warning.

This behaviour is also exhibited if I try to modify the Default Style, or any other style that I have tried to modify,

I cannot change the style because I do not get a style modification window.

Creating a new custom style descending from Default / Background and only changing the font colour and cell background when applied as conditional formatting to a cell whose date format is yyyy-mm-dd results in a decimal number.

Created attachment 163943 [details]
This is an entire sheet copied from the original workbook.

Your correction works on my system.  However even copying and pasting that entire cell to the spreadsheet that has the problem does not cure it. I am attaching an entire sheet from the book in the off  chance there is something about the sheet itself (macro, cross reference, whatever) that is triggering this.

Whatever the reason, this sheet and the workbook it is from, never had this problem until recently.  And the same problem is exhibited if I open files last viewed in 2018 and 2019.

This is causing a major difficulty.  I have tried to resolved this by removing the customs style used by the conditionally formatted cells displaying the problem.  However, this has proved to be impossible.  Regardless of the view in the Style pane selecting and deleting does not remove the custom style.

In the hierarchical view the style is removed from the list, but opening custom style view shows it still present.  Switching back to the hierarchical view and the 'deleted' style reappears.

Likewise, find and search and replace do not allow me to search for a custom style> I can choose only from: Default, Excel Builtin Normal (whatever that is), and Report Page.

On the affected spreadsheet I cannot modify any style.  I can add but I cannot modify, the dialogue box does not display.

If I go to the affected cells and remove the conditional formatting then the display reverts t o the direct formatting applied.  However, if I then attempt to apply conditional formating using one of the provided styles, say Attention, then the direct formatting is ignored.  Looking at the cell formatting shows the correct format but the display does not match.

I am now faced with the prospect of having to rebuild this entire spreadsheet, and all of its formulas and formats, because of this issue.  If I could roll back to an earlier version of LO then I would do that but this does not seem to be possible with FreeBSD.

Sorry but your last attachment it's a text file not and ods.

Created attachment 164167 [details]
Single sheet displaying conditional formatting bug

This sheet displays, to me on my desktop FreeBSD system, the date of birth conditional format misrepresenting the date.  It also exhibits the inability to modify or delete the custom format style.  Finally, the Find & Replace function does not display the available styles in the find selector. The only available values are Default and Excel Built-in Normal.

I see while defining conditions showed format it is not right.

But negative warning has a numeric style, not date. Modifying it to date, it is not showed right editing CF, but seems to work fine on the sheet.

In any way there is a bug about what is showed on editing the CF.

Version: 24.2.4.2 (X86_64) / LibreOffice Community
Build ID: 420(Build:2)
CPU threads: 16; OS: FreeBSD 14.1; UI render: default; VCL: qt5 (cairo+xcb)
Locale: en-CA (C.UTF-8); UI: en-US
Calc: threaded

Example spreadsheet from 2020-08-11 displays this behaviour in LO-24.2.

I am experiencing the same problem as is being discussed here. At least number-formatting and text properties such as strike-through that I have set on a cell are overridden by the style.

To my understanding the problem is that style formatting is taking precedence over direct formatting. It should be the opposite!
Furthermore we need the ability to specify which properties a style affects. i.e. make it possible to define a style that only sets the background color or borders.

Ideally each property (background color, bottom border, top border, text-strike-through, etc.) would be applied in this order:
1. Is it specified with direct formatting? Apply direct formatting, done.
2. Is it specified with the style? Apply the style, done.
3. Is it specified with the parent of the style? Apply the parent of the style, done.
4. Repeat 3. for each parent until the "Default" style is reached.

This way a style can be used for the whole sheet, child-styles can be used for sections of the sheet and a direct formatting can be used for exceptions.

Right now it seems one has to choose either direct formatting or styles and cannot combine them at all.


LibreOffice 24.8.1.2

Thanks for an otherwise amazing tool!