Download it now!
Bug 103793 - FORMATTING: Conditional formatting doesn't respect category of number-format (Documentation per Comment 8).
Summary: FORMATTING: Conditional formatting doesn't respect category of number-format ...
Status: CLOSED DUPLICATE of bug 117715
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
Version:
(earliest affected)
5.1.6.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL: https://help.libreoffice.org/6.3/en-U...
Whiteboard:
Keywords:
: 122907 (view as bug list)
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2016-11-08 21:55 UTC by Jan
Modified: 2019-10-26 19:40 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Demo file, created following the descripted steps. (7.69 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-11-08 21:55 UTC, Jan
Details
Another demo (8.45 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-01-15 15:12 UTC, Timur
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jan 2016-11-08 21:55:29 UTC
Created attachment 128591 [details]
Demo file, created following the descripted steps.

Steps to reproduce:

1. Create a new spreadsheet.

2. Create a new style (e.g. named "asCurrency"). On tab "numbers" choose "currency" as category. Further choose a background-color.

3. Create a new style (e.g. named "asDate"). On tab "numbers" choose "date" as category.

4. Open the dialog for conditional formatting for cell A1.

5. Create a condition with the formula $A$2="D" and link it to the style "asDate".

6. Create a condition with the formula $A$2<>"D" and link it to the style "asCurrency".

7. Write a date to cell A1 (e.g. 4.3.).

8. Write "D" to cell A2.

Result:

Switching between to the two examined conditions effects the background-color, but not the numbers->category. It seems impossible to get a currency format through changing A1 and A2 values in this state. The value is always either viewed as date or as plain number.

Expected:

The condition NOT "D" in cell A2 should influence A1s format, including its numbers->category. In this case A1s value should appear as currency (e.g. 42.433,00 € instead of 04.03.2016).

Variant:

Sometimes it's just the other way around so that the date-format won't appear. 


Best regards,
Jan
Comment 1 Buovjaga 2016-11-19 21:24:10 UTC
Repro from scratch.

Arch Linux 64-bit, KDE Plasma 5
Version: 5.3.0.0.alpha1+
Build ID: f533b3f39956fe8028c1f7f7cc3c37b0feed8b57
CPU Threads: 8; OS Version: Linux 4.8; UI Render: default; VCL: kde4; Layout Engine: new; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on November 19th 2016
Comment 2 QA Administrators 2018-01-16 03:29:07 UTC Comment hidden (obsolete)
Comment 3 Thomas Lendo 2018-03-08 22:37:11 UTC
Still reproducible.

Version: 6.1.0.0.alpha0+
Build ID: f23651ff10da2d4bbbc47d0152b5cc321c29f0d1
CPU threads: 4; OS: Linux 4.13; UI render: default; VCL: gtk3; 
Locale: de-DE (de_DE.UTF-8); Calc: group
7. March 2018
Comment 4 Timur 2019-01-15 15:12:07 UTC
Created attachment 148335 [details]
Another demo

Until Bug 93300 this worked because style formatting used to override direct formatting. But, from there, direct formatting overrides style formatting and we need not to apply it. 

Demo file is not quite correct. A1 has Date format. So any change in A2 will not CF effect A1. I can't say how we make it work i.e. remove that format. Note Bug 122697.

I sometimes repro from scratch, depending on how I set cell A1. But I also don't as in attached another demo ODS. Tried with 6.3+.
So I kindly ask you all to test more from scratch.
Comment 5 Timur 2019-01-15 17:01:39 UTC
*** Bug 117715 has been marked as a duplicate of this bug. ***
Comment 6 Buovjaga 2019-01-19 22:58:07 UTC
(In reply to Timur from comment #4)
> Demo file is not quite correct. A1 has Date format. So any change in A2 will
> not CF effect A1. I can't say how we make it work i.e. remove that format.
> Note Bug 122697.

I still repro from scratch with latest master.

Like you said, the fix for bug 93300 causes this to happen. I did a bibisect to be sure and I tested from scratch with the blamed commit vs. the previous one. https://gerrit.libreoffice.org/plugins/gitiles/core/+/2c728b0ae7f52691b1a3d5ab555225f5e8ac95a5%5E!/

Others blaming the same are bug 108086 and bug 114860

You said "depending on how I set cell A1", but how exactly? I don't set it at all, just use default formatting.

The demo file already has the date formatting applied due to "4.3." having been entered in LibO 5.1.6.

It is more convenient to have a test file with steps 1-3 and do all the further steps from scratch.
Comment 7 Buovjaga 2019-01-19 23:04:09 UTC
Ok, I get it: this is just the way the things are after the fix for bug 93300. When we type something to a cell, the direct number-formatting is inferred. Thus, conditional formatting is not allowed to touch that particular aspect (in the example, color was not directly formatted. Closing as notabug.
Comment 8 Timur 2019-01-21 11:42:50 UTC
While behavior itself may be NotaBug, I'm in favor of converting those bugs to Documentation, to explain that:
- direct formatting overrides style formatting (logical, but needs to be written, guess that's "If a style was already assigned to a cell, it remains unchanged." but I'd rather use "direct formatting".)
- when we type something to a cell, the direct number-formatting is inferred (not explained and can confuse)
Comment 9 AndyK 2019-05-04 23:45:41 UTC
I have a little table where I want positive values lower or equal 10 to be displayed as percentage "#,##0%" and values higher than 10 as normal decimal values with format "#,##0".

1. I made a style "percentage" based off of the standard/default style, changed the number formatting to percentage "#,##0%" and selected the bold font type (to verify).
2. I made a style "number" based off of the standard/default style, changed the number formatting to decimal "#,##0" and selected the italic font style (to verify)
3. I typed in A1 the value 2.5, A2 the value 0.5, A3 the value 125 and in A4 the value 30.
4. I created a conditional formatting with condition 1 "value is below 11" set use style "percentage" and condition 2 "value is equal or greater than 11" use style "number". Range A1:A4

I thought conditional formatting would allow me to do this, but it is not as I can't change number formatting using conditional formatting.

If I manually set the style "percentage" or "number" to a cell, the correct number format will be displayed.

--------

While writing this and verifying every step along I found a workaround:

1. Enter your values
2. Select the cells you want to have the conditional formatting
3. "Edit" -> "Clear Cells (Backspace)" -> select only "Format"
4. Create styles (if necessary)
5. Create/Apply conditional formatting

The step to clear all formats is the key action to have this working.
Even though the user has not explicitly applied a format did so implicitly.

Imho this information should be included in the help documentary.
Comment 10 Xisco Faulí 2019-06-26 09:52:33 UTC
*** Bug 122907 has been marked as a duplicate of this bug. ***
Comment 11 Eike Rathke 2019-10-26 19:39:27 UTC
Rather fix it than document it. While hard > conditional > style is logical also to me (but only implemented for number formats), it seems not to be what is expected. See duplicate of.

*** This bug has been marked as a duplicate of bug 117715 ***