Bug 82765 - FORMATTING: Conditional formatting changes digits behind the decimal point
Summary: FORMATTING: Conditional formatting changes digits behind the decimal point
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: Other Windows (All)
: highest normal
Assignee: Not Assigned
Whiteboard: BSA
Depends on:
Reported: 2014-08-18 11:45 UTC by bugquestcontri
Modified: 2019-10-05 06:46 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

File with examples and exceptions (20.30 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-08-18 11:45 UTC, bugquestcontri
second example file - bug was not observed (12.12 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-09-28 07:23 UTC, bugquestcontri
observations about conditional and customized format and decimal places (93.41 KB, application/vnd.oasis.opendocument.text)
2014-09-28 07:24 UTC, bugquestcontri
Patch with possible behaviour (3.52 KB, patch)
2014-11-30 20:12 UTC, Renato Ferreira

Note You need to log in before you can comment on or make changes to this bug.
Description bugquestcontri 2014-08-18 11:45:57 UTC
Created attachment 104815 [details]
File with examples and exceptions

Problem description: 
When cells are formated as Numbers with 1 or more decimal places (Format: 0.0), and  conditional formatting is set, whenever the condition is met, the decimals disappears and the figure in the cell is displayed as rounded up or down. 

However there are a few exceptions. In the attached file the exceptions are marked with a red frame.

Steps to reproduce:
1.Open a Calc file
2. Format an area as Number using format 0.0
3  Add conditional formatting 
4. Enter figures with a least one decimal place

Current behavior:
Conditional formatting changes number of decimal places to 0. 

Expected behavior:

Conditional formatting does  not changes the number of decimal places

Build ID: 185f2ce4dcc34af9bd97dec29e6d42c39557298f

Operating System: Windows XP
Version: release
Comment 1 ign_christian 2014-08-18 15:04:23 UTC
Hi.. I can't reproduce from scratch in LO - Ubuntu 12.04 x86.

In your attached file, style used in particular CF is screwed up. For example, style "Untitled3" has number format #" "###" "###" "###" "###" "##0

Could you give steps to repro from scratch?
Comment 2 ign_christian 2014-08-18 15:05:34 UTC
Lowering the importance to normal until problem identified
Comment 3 bugquestcontri 2014-08-19 00:41:34 UTC
I looked again into the file I attached yesterday and found that in style Untitled 1, 2, and 3 the number format was messed up like you described it (#" "###" "###" "###" "###" "##0)

When I created the attached test file I did the following
- Select column
- right click select Format cell > Numbers
- set format to 0.0
- clicked OK
- selected column
- Format > Conditional format > conditions
- select condition (e. g. greater than) 
- select New Style > Background > select ONLY a color > OK
- added second and third CF the same way
- clicked OK
>> I did NOT look into the number format when I created a New Style
>> I only checked Number Format by right clicking to the cell > Format cells > Numbers and there it was always - even after applying the new style Untitled -  as set (0.0)

I just repeated the above described procedure again and got the same result.

With your hint about the number format in Untitled format I looked into the Untitled Style > Modify > Numbers > Decimal Place = 1 and then all cells with this format show correct format 0.0.

For my understanding the bug is now that the number format first applied to a cell remains when it is checked by right clicking to a cell and go Format Cells > Numbers but in reality the format from the CF is applied but not visible when checking the applied format directly at the cell by right clicking and go Format Cells > Numbers.

Side note on importance settings: You have more experience, thus just go ahead.
Comment 4 Markus Mohrhard 2014-08-30 22:01:41 UTC
I don't see a bug here. If the conditional format is applied you get the number format from the CF style. The Format Cells dialog only shows hard formatting (which might be overwritten by conditional formatting).

The hierarchy is: conditional formatting, hard formatting, style. The formatting is taken from the first hit and in your case it is the conditional formatting. I have no idea how you think that we should apply the hard formatting despite the conditional formatting (which specifies a number formatting as well) being applied
Comment 5 bugquestcontri 2014-09-28 07:22:25 UTC
I made another test using the same LibO version and OS but could not reproduce the bug I observed first. When I reported the bug it really was bothering me.

Now it work almost like I expected. Example is attached.

However I observed something with changing of a customized format when increasing the number of decimal places. 

All is described with screen shots in the attached Writer files. This file also contains thoughts about expectations from my side. But as I don't know the specifications, I cannot make a statement in respect it is a bug or an enhancement request. 

Please have a look and advice.
Comment 6 bugquestcontri 2014-09-28 07:23:36 UTC
Created attachment 106983 [details]
second example file - bug was not observed
Comment 7 bugquestcontri 2014-09-28 07:24:31 UTC
Created attachment 106984 [details]
observations about conditional and customized format and decimal places
Comment 8 Renato Ferreira 2014-11-30 20:12:08 UTC
I could replicate the behaviour described in the attachment above.

My understanding is that "non-canonical" user-defined styles are, first, identified as number styles, and those are defined in the code by the set: (flag) show thousand separator, (flag) show red negative numbers, (int) precision, (int) leading zeroes. Then when the user tries to edit their strange style (in this case containing spaces, but could be any characters) using the UI selectors, basically those variables get changed accordingly, but a new, "canonical" style is generated instead (thus without the user-defined features).

I propose that, since those strange styles could be really anything, to disable the UI selectors if the user is defining one of those non-canonical styles. That is, a style such as #0"mysep".00 can be defined and works, but the user is not led into trying to edit the style using the UI selectors, just to have it broken as described. If, however, the user defines a canonical style such as 0.0000, they still get to edit it using the UI elements normally.

I have prepared a patch that causes this behaviour, in case anyone would like to give it a try.

The only issue is that I am not sure, at the moment, how to do the same in the sidebar style selector. I looked at the code and it seems very detached from the rest, only receiving a few variables, and it seems not to have access to the format string itself, which would be necessary to induce the same behaviour.

If you think this is a good solution, I would welcome directions on how to deal with the sidebar issue.
Comment 9 Renato Ferreira 2014-11-30 20:12:40 UTC
Created attachment 110275 [details]
Patch with possible behaviour
Comment 10 Kevin Suo 2015-03-09 03:37:29 UTC
This is NOTABUG, but is the expected behaviour.

For attachment 104815 [details], althrough the Column A is formatted as "0.0", but when you go to "Format - Conditional Formatting - Manage - A1:A1048573", you will see that if the condition is met then style "Untitled3" is used. Further when you go to style management dialog, you will see that for style "Untitled1", numbers are mannually formatted as "#" "###" "###" "###" "###" "##0". Because of this, the numbers should truely be formatted as 0 decimal place.

If you set the number format of "Untitled1" to "General", the decimal places for Column A would be as you expected.

Set as NOTABUG. Please set back to UNCONFIRMED is someone disagree. Thanks.
Comment 11 bradly 2019-10-05 06:46:02 UTC Comment hidden (spam)