Description: Unlike other settings, Number Format in a conditional formatting style doesn't apply if a cell has a number format applied by direct formatting Version: 6.0.3.2 Build ID: 8f48d515416608e3a835360314dac7e47fd0b821 CPU threads: 4; OS: Linux 4.13; UI render: default; VCL: gtk2; Locale: ru-RU (ru_RU.UTF-8); Calc: group Steps to Reproduce: 1. Create a new Calc doc 2. In cell A1 enter number 200, in cell B1 enter number 200 3. Set for A1 Format as Number (Ctrl+Shift+1) 4. Create a new style "Good_Currency" from existing style "Good", with format number Currency 4. Create for A1 Condition (Format-Conditional Formatting-Condition) with Cell value is - greater than - 199 and in drop-down list Apply style select our Good_Currency 5. Create for B1 Condition (Format-Conditional Formatting-Condition) with Cell value is - greater than - 199 and in drop-down list Apply style select our Good_Currency 6. Look at A1 and B1. In B1 there is currency, in A1 there isn't! Actual Results: Number Format in a conditional formatting style doesn't apply if a cell has a number format applied by direct formatting Expected Results: Number Format in a conditional formatting style always apply Reproducible: Always User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:60.0) Gecko/20100101 Firefox/60.0
Created attachment 142215 [details] Screenshot with different results after conditional formatting
Created attachment 142216 [details] File-example with different results after conditional formatting
no repro Version: 6.0.5.0.0+ Build ID: efd59fd1d7fc9d955a2b924f247709201f2281c5 CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group Version: 6.1.0.0.alpha1+ Build ID: 8eb15f031003f6431d41853ff70e307b617d1a5a CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group works for me, if not agree you can reopen as unconfirmed
Reproducible with Version: 6.1.0.0.alpha1+ (x64) Build ID: db04be037b611e296ef9f2542322c52ed82d7a2b CPU threads: 12; OS: Windows 10.0; UI render: GL; Locale: ru-RU (ru_RU); Calc: CL STR: 1. Create a new empty spreadsheet 2. Put a number into A1 (e.g., 1) 3. Apply manual formatting to A1: e.g., Number format: 0 decimals; Font: Liberation Mono size 20; Font color: Indigo; Horizontal alignment: Left; Background: Orange. 4. Apply conditional formatting to A1: e.g., Condition: cell value greater than 0 => New style: Number formatting: Currency; Font: Liberation Serif size 8; Font color: Red; Horizontal alignment: Center; Background: Light Lime 4. Actual result: every setting from Conditional formatting had overwritten the manual settings - *except for number formatting*, which is still Number 0 decimals, not Currency.
If this is the case then the actual bug is that all other formatting is overridden by the conditional format. Hard cell format attributes should override conditional formatting.
Per Roman's steps, this is NotaBug. This behavior started from Bug 93300. Direct cell format (from step 3.) now overrides conditional formatting. Per Mike's steps, it's a bug, but opposite, as Eike wrote: Expected result: Settings from Conditional formatting shouldn't overwrite direct formatting (only number formatting remained). *** This bug has been marked as a duplicate of bug 103793 ***
*** Bug 128403 has been marked as a duplicate of this bug. ***
Note that bug 128403 has a test case attached http://bugs.documentfoundation.org/attachment.cgi?id=155326 that even overrides the hard date format attribute with a conditional currency format in some yet unclear circumstances.
Actually not a duplicate.
Looking at related code (sc/source/core/data/patattr.cxx ScPatternAttr::GetFont() (and ScPatternAttr::FillToEditItemSet())) it seems to be on purpose that conditional formatting takes precedence over hard attribution. As a user I would expect exactly the opposite though, because as is there is no way to override formatting for some cells in a range of conditionally formatted cells. But also Excel lets conditional formatting take precedence over hard attribution, citing from https://support.office.com/en-us/article/Manage-conditional-formatting-rule-precedence-E09711A3-48DF-4BCB-B82C-9D8B8B22463D "What happens when a conditional format and a manual format conflict? For a range of cells, if a formatting rule is evaluated as True, it takes precedence over an existing manual format." Hence, changing the current behaviour is not a good idea. We should rather re-evaluate if the number format of a conditional format shouldn't also take precedence over a manual/hard number format. What does Excel do for number formats there? I guess the same, no exception.
The current behaviour hard > condition > style is a result of fixing bug 93300. It seems other users have the same expectations I have.. You'll also find several "how can I override conditional formatting in Excel" questions on the net. This sucks and looks like a dead end.
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, taking duplicates of see-also-bugs and Excel behaviour into account.
*** Bug 114860 has been marked as a duplicate of this bug. ***
*** Bug 103793 has been marked as a duplicate of this bug. ***
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/4dfb22acfe30f9322ecc0ad9d01b17a3814c87a2 Related: tdf#117715 tdf#128403 Couple ATTR_LANGUAGE_FORMAT ATTR_VALUE_FORMAT It will be available in 6.4.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/2b0626161d3ef7c4a51007018d13ec391d3a2b04 Resolves: tdf#117715 Conditional format takes precedence; reverts tdf#93300 It will be available in 6.4.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/81552 for 6-3
I have tested with my test document "Test_bedingte_Formatierung_Format_Datum.ods" from Bug 122907 with Version: 6.4.0.0.alpha1+ (x64) Build-ID: 2b0626161d3ef7c4a51007018d13ec391d3a2b04 CPU-Threads: 4; BS: Windows 10.0 Build 18362; UI-Render: GL; VCL: win; Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE Calc: threaded Result: It looks good - as expected!
"Nice" :( So we again end up with table cells whose style cannot be changed because they have a conditional format? That sounds like bad news to me (and I understand for some people in some cases it may be good news). Isn't there a better way of solving this issue? I can imagine e.g. Libreoffice warning the user when he's creating a cond format on some cells which already contain hard formatting. This way the user would know there might be some more actions needed for his cond format to take over, and anybody would have the chance to locally override whatever format the cell has.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-3": https://git.libreoffice.org/core/commit/0b9e0f20763f0e723d66f31aee85205deec9f6f8 Resolves: tdf#117715 Conditional format takes precedence; reverts tdf#93300 It will be available in 6.3.4. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I've removed some of the conditional formatting from the spredsheet and replaced it with what looks to me the same thing but with different Style names. I'm still having formatting issues but the date now seems to render correctly in 6.3.5.2. In any case it still seems like an issue where things worked in 6.2.8.2 and not in 6.3X.