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.
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.
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).
Sometimes it's just the other way around so that the date-format won't appear.
Repro from scratch.
Arch Linux 64-bit, KDE Plasma 5
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
** Please read this message in its entirety before responding **
To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.
There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.
If you have time, please do the following:
Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/
If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.
Please DO NOT
Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not
appropriate in this case)
If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/
2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword
Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa
Thank you for helping us make LibreOffice even better for everyone!
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
Created attachment 148335 [details]
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.
*** Bug 117715 has been marked as a duplicate of this bug. ***
(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.
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.
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)
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.
*** Bug 122907 has been marked as a duplicate of this bug. ***
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 ***