Bug Hunting Session
Bug 38956 - FORMATTING: Negative Currency Numbers Don't Show Red and other number format prefixes are lost
Summary: FORMATTING: Negative Currency Numbers Don't Show Red and other number format ...
Status: CLOSED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.1 release
Hardware: Other All
: high critical
Assignee: Eike Rathke
URL: CONFIRMED
Whiteboard: numberformat target:3.4.3
Keywords: regression
: 37658 (view as bug list)
Depends on:
Blocks: mab3.4
  Show dependency treegraph
 
Reported: 2011-07-04 12:41 UTC by bugzilla
Modified: 2012-02-16 07:23 UTC (History)
11 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheet with Red Currency Formatting, But No Red (7.42 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-07-05 16:18 UTC, bugzilla
Details

Note You need to log in before you can comment on or make changes to this bug.
Description bugzilla 2011-07-04 12:41:19 UTC
Create a new spreadsheet.

Fill in negative numbers without decimals.

Highlight cells (all or specific - doesn't matter).

Format -> Cells
Category: Currency
Select Fourth option: -$1234.00
"Negative Numbers Red" is checked by default.
Hit OK.


Numbers do not change to red.  However, currency symbol is applied and two decimal zeroes are applied as for currency.


Alternatively:

Select Second option: -$1234.00
Check "Negative Numbers Red".
Hit OK.


Numbers do not change to red.  However, currency symbol is applied and two decimal zeroes are applied as for currency.


Selecting other options (such as the ones with "USD" in the format) does work correctly - it is only these two options which do not appear to work correctly.

Information:

LibreOffice 3.4.1 
OOO340m1 (Build:103)

Macbook Pro 8,2
Mac OS X - Version 10.6.8
2.3GHz Intel Core i7
8GB 1333 MHz DDR3
Comment 1 Jeffrey 2011-07-04 19:48:52 UTC
Reproduced in LibreOffice 3.4  340m1(Build:12) on OpenSuse for Linux.

Followed the same steps and the same phenomenon occurred.

It is strange that these two does not work because the Format Code looks correct.
Comment 2 Rainer Bielefeld Retired 2011-07-04 22:06:33 UTC
OA -> All due to Comment 1
Version modified due to original report

I was not able to reproduce the problem with "LibreOffice 3.4.1RC1 – WIN7  Home Premium  (64bit) English UI [OOO340m1 (Build:103)]", negative values with currency settings due to report always were shown red.

@reporter, Jeffrey :
The problem might be related to particular LibO settings on your PC, can you please check that?
Comment 3 bugzilla 2011-07-05 08:48:02 UTC
(In reply to comment #2)
> OA -> All due to Comment 1
> Version modified due to original report
> 
> I was not able to reproduce the problem with "LibreOffice 3.4.1RC1 – WIN7  Home
> Premium  (64bit) English UI [OOO340m1 (Build:103)]", negative values with
> currency settings due to report always were shown red.
> 
> @reporter, Jeffrey :
> The problem might be related to particular LibO settings on your PC, can you
> please check that?

Sure, how?
Comment 4 Rainer Bielefeld Retired 2011-07-05 10:32:36 UTC
bugzilla@thomasquinlan.com 
May be you attach a sample document to exclude that I made a mistake? additionally you can test around a little and observe whether that might have an influence, because I'm a little worried that WIN should not have such a bounded problem. But of course, it's possible.
Comment 5 Markus Mohrhard 2011-07-05 14:30:04 UTC
@Rainer: If you don't have problems with this, can you check if LibO crashs if
you hit the "Negative Numbers Red" checkbox.

As much as I see from a look into the code both problems might be related.
Comment 6 bugzilla 2011-07-05 16:18:55 UTC
Created attachment 48792 [details]
Spreadsheet with Red Currency Formatting, But No Red
Comment 7 bugzilla 2011-07-05 16:21:36 UTC
(In reply to comment #4)
> bugzilla@thomasquinlan.com 
> May be you attach a sample document to exclude that I made a mistake?
> additionally you can test around a little and observe whether that might have
> an influence, because I'm a little worried that WIN should not have such a
> bounded problem. But of course, it's possible.

Hi,

I've attached a sample.  The numbers should show up as currency, but won't show up as red (on a Mac).

This initially came to my attention because I keep my budget in ODS, and when I switched from Open Office to Libre, there were no negatives.  (I list income as negative and add outgoing to get to zero.)

I checked to make sure it wasn't my spreadsheet before filing the bug, and as you can see from the attachment, it's not.  This happens with any new spreadsheet.

Thanks.
Comment 8 Markus Mohrhard 2011-07-05 21:08:53 UTC
Rainer, forget what i wrote. They might be related but I don't understand yet what's going wrong in this case here.

If I find some time I will have a look at this problem but for now the crash with format -$1,234,-- is more important.
Comment 9 Rainer Bielefeld Retired 2011-07-05 21:55:21 UTC
@markus:
With reporter's sample I see all negative numbers in black
Creating my own sample and selecting the same formatting (with the same formatting string) shows number in red. 
Copy / paste of such a (red) negative number to reporter's sample shows it in black. 
In my document I see that fourth USD formatting in picker red, in reporter's document it looks black.
I also have some difficulties to understand all relations, but I still share your suspect that it has to do "somehow" with the "negative red" checkbox. 

Yes, I see crashes from "Bug 38963 - Format Cells -- Currency Checkboxes Cause Calc to Crash", i believe it's always (mostly?) the "negative red" checkbox causing the crash.

I assigned but to you because of suspect concerning relation to Bug 38963, please feel free to reassign if it's not your area!
Comment 10 Michael Askew 2011-07-17 18:44:37 UTC
I ran into this problem today (as well as the negative number in red checkbox crash). Glad to see it's already been filed. For anyone curious, I made a screen recording of the problem:

http://dl.dropbox.com/u/128380/LibreOffice%20Spreadsheet%20Bug%20with%20Currency%20Formatting.mov
Comment 11 Eike Rathke 2011-08-01 05:34:41 UTC
Grabbing issue, raised importance and severity because not only [RED] on currencies is lost, also other [prefixes] of user-defined number formats may be affected. These are lost when loading and saving the document. Another symptom is the weird behavior of the number formats dialog when switching a red currency to not-red. I'll come up with a solution.
Comment 12 smmess 2011-08-01 12:27:43 UTC
I also noticed an issue when formatting 0 values. I can't change the color of 0 values and when I save, close, and reopen my file, they change to blue.
Comment 13 Eike Rathke 2011-08-01 12:44:04 UTC
(In reply to comment #12)
What format code do you use to change the color of 0 values?
Comment 14 Eike Rathke 2011-08-01 13:28:36 UTC
*** Bug 37658 has been marked as a duplicate of this bug. ***
Comment 15 smmess 2011-08-02 13:11:36 UTC
(In reply to comment #13)
> (In reply to comment #12)
> What format code do you use to change the color of 0 values?

Here are the steps of what happens
Create a new spreadsheet

Type 0 in cell A1
Format A1 and change the number format to currency with negative numbers red and add ;[BLUE][$$-409]#,##0.00 to the end so you get this:
[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;[BLUE][$$-409]#,##0.00
Click okay after changing
Note that if you go back to the number format it has changed to
[$$-409]#,##0.00;-[$$-409]#,##0.00;[$$-409]#,##0.00
Even though it removed the [BLUE], it is still blue for 0 (also, it is still red for negatives even though it removed the [RED]).

Save, close, and reopen the file.
Cell A1 is still blue (negatives are no longer red) and note that the number format has changed to:
[>0][$$-409]#,##0.00;[<0]-[$$-409]#,##0.00;[$$-409]#,##0.00

Type 0 in cell A2
Format A2 and change the number format to currency with negative numbers red and add ;[GREEN][$$-409]#,##0.00 to the end so you get this:
[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;[GREEN][$$-409]#,##0.00
Click okay after changing

Note that at this point we have 2 user defined number formats, our blue one as
[>0][$$-409]#,##0.00;[<0]-[$$-409]#,##0.00;[$$-409]#,##0.00
and our green one as
[$$-409]#,##0.00;-[$$-409]#,##0.00;[$$-409]#,##0.00

Note that A1 is blue and A2 is green.

Save, close, and reopen the file.
Both A1 and A2 are now blue.
There is only 1 user defined number format like this
[>0][$$-409]#,##0.00;[<0]-[$$-409]#,##0.00;[$$-409]#,##0.00

It looks like when saving, closing, and reopening the file, both user defined number formats were changed to be identical and it deleted the second one which was green and kept the first one which was blue.
Comment 16 Eike Rathke 2011-08-02 14:00:19 UTC
(In reply to comment #15)

This is exactly what this bug already is about, the change I'm working
on will fix this.

> Save, close, and reopen the file.
> Cell A1 is still blue (negatives are no longer red) and note that the number
> format has changed to:
> [>0][$$-409]#,##0.00;[<0]-[$$-409]#,##0.00;[$$-409]#,##0.00

I don't quite understand why A1 was still blue then after having
reopened the document, but the fix already gets things right so I won't
investigate further.

> Type 0 in cell A2
> Format A2 and change the number format to currency with negative numbers red
> and add ;[GREEN][$$-409]#,##0.00 to the end so you get this:
> [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;[GREEN][$$-409]#,##0.00
> Click okay after changing
> 
> Note that at this point we have 2 user defined number formats, our blue one as
> [>0][$$-409]#,##0.00;[<0]-[$$-409]#,##0.00;[$$-409]#,##0.00
> and our green one as
> [$$-409]#,##0.00;-[$$-409]#,##0.00;[$$-409]#,##0.00
> 
> Note that A1 is blue and A2 is green.
> 
> Save, close, and reopen the file.
> Both A1 and A2 are now blue.
> There is only 1 user defined number format like this
> [>0][$$-409]#,##0.00;[<0]-[$$-409]#,##0.00;[$$-409]#,##0.00

Semantically
[>0][$$-409]#,##0.00;[<0]-[$$-409]#,##0.00;[$$-409]#,##0.00
is identical to 
[$$-409]#,##0.00;-[$$-409]#,##0.00;[$$-409]#,##0.00
and stored identically in the file format (explicitly using
style:condition descriptors for [>0] and [<0])

> It looks like when saving, closing, and reopening the file, both user defined
> number formats were changed to be identical and it deleted the second one which
> was green and kept the first one which was blue.

Already lost when saved.