I'm now a little wondering about the formation of "#,##0.00;[RED]-#,##0.00". I'm not an expert with it but are the 5 digits Calc is showing me after applying it correct?

Well, this is reproducible in 4.1.0.4 under Win7x64. The Format string changes on save and reopening: it becomes “#,#####.”. This is a bug.

Still, the original format string is illegal (and logicaly meaningless). #,##0.00 means “if absent, omit all digits to the left of decomal separator, and two digits to the right of it, and show the third to the right, after which show a dot and two zeroes”. How is it possible to omit two first fractional digits and show third?

I don't know the reason for it, but the correct format string would be #,000”.00” (it shows the same, and persist after save/reopen).

I hesitate to set it to NEW, as it is logically inconsistent bug. Someone more experienced advise would be very helpful.

I do not reproduce with LO 4.1.3.0.0+ under Ubuntu 13.04 x86-64 with French locale.

With the format "#,##0.00;[RED]-#,##0.00", you should see: 1.00 (only 2 decimals)
If you want 5 decimals, you need with "#,##0.00000;[RED]-#,##0.00000".

Please, could you try again with the current version of the 4.1 branch which is 4.1.1? You may want try too the first RC of LO 4.1.2 which is available at http://dev-builds.libreoffice.org/pre-releases/

Best regards. JBF

changed component to spreadsheet and made the summary more expressive (hopefully).

In the development version 4.1.2.1 the bug still exists (even with 5 digits).

Please, could you attach your test file?

Best regards. JBF

(In reply to comment #3)
> I do not reproduce with LO 4.1.3.0.0+ under Ubuntu 13.04 x86-64 with French
> locale.

It really works differently under Windows. With my Ubuntu, I cannot reproduce it, too.
But under Windows, it is easily reproducible with 4.1.1.2 and 4.1.2.1.

(In reply to comment #6)
> Please, could you attach your test file?

As described in comment 2, in affected systems, on saving and reopening, the format string changes. So it's not possible to attach a test file; but the steps in comment 0 are pretty accurate.

The only thing that I cannot understand is that the problem is clearly visible under Windows, but is not under Ubuntu; though the issue platform says "Linux (All)"...

Please, could you check what you have as option under Ubuntu and MS-Windows for:
menu Tools > Options > LibreOffice Calc > Calculate > Limit decimals for general number format ? For me, under Ubuntu, the checkbox is not checked.

Best regards. JBF

> The only thing that I cannot understand is that the problem is clearly visible > under Windows, but is not under Ubuntu; though the issue platform says "Linux > (All)"...

I'm using Ubuntu 13.10 dev.


> Please, could you check what you have as option under Ubuntu and MS-Windows for:
> menu Tools > Options > LibreOffice Calc > Calculate > Limit decimals for > general number format ? For me, under Ubuntu, the checkbox is not checked.

It is not checked on my system too.

(In reply to comment #8)
> Please, could you check what you have as option under Ubuntu and MS-Windows
> for:
> menu Tools > Options > LibreOffice Calc > Calculate > Limit decimals for
> general number format ? For me, under Ubuntu, the checkbox is not checked.

Unchecked in 4.1.1.2 under Win7x64

This seems to be a possible profile issue - I cannot reproduce in Linux while reporter can but Mike can't.

I suggest resetting profile and trying with latest release
https://wiki.documentfoundation.org/UserProfile

If you still experience the issue with a fresh profile and the latest release please set to UNCONFIRMED and we'll go to the next step of trying to figure out what's going on.


Thanks for helping us out!

Even after deleting ~/.config/libreoffice and testing with the upstream version 4.1.3.2 I'm still able to reproduce this problem as described in the startpost.

This has direct relation to regional settings.

The original format string ("#,##0.00;[RED]-#,##0.00") should be read like this:
"Show at least one decimal digit before decimal separator, and at least two after; show thousand separators; for negatives, show the sign and mark the number red". Here, the comma (",") stands for thousand separator, the dot (".") for decimal separator.

But in the format string, these symbols are locale-dependent. This format string is only valid for locales where the comma is used for thousands, and the dot is decimal separator.

In other locales, the corresponding characters from the locale must be used instead. For example, here in Russia, we use comma (",") as decimal separator, and space (" ") as thousands separator. In this case, the correct format string looks like this: "# ##0,00;[RED]-# ##0,00". And it works.

(See more details here: https://help.libreoffice.org/Common/Number_Format_Codes)

If I manually set another locale for a cell (say, English (US)), I can use that locale codes.

But if I use wrong codes (like when I use original reporter's code for Russian locale cell), the bug appears.

Sorry, in the third line of comment 13, "and at least two after" should be read as "and exactly two after".

@Mike - with that comment it seems like this is just user error? Basically if you put in a valid format, it works fine

(In reply to comment #15)
No, not exactly.

As I stated in comment 2, when I wrongly assumed that this format string is illegal from the start (didn't take the locale dependency into the account, sorry for that), the software should not change the format string itself silently, even if it is illegal. It could perform a check on that, and emit a notification on illegal entry; otherwise, it should keep the entered value. That is the bug. And doing that change on save-and-reopen is even less forgivable, as this looks like silent data corruption at a stage when user is likely to overlook this.

Here arise other related concerns, that deserve to have their own issue reports.

1. When a cell locale is directly set, corresponding format codes must be used. A user may be unaware of it, or may be unaware of specific characters, and the documentation is not very verbose on this, too. It is desirable to at least have a notification area in the formatting dialog showing active codes, or (better) have buttons inserting those characters (a keyboard may lack the required keys);

2. There is no way (at least in UI) to change the locale of a cell without dropping the format string. But it should be possible, especially given that LO does this internally when the locale of a cell is "default", and this default is changed in LO settings;

3. Possibly, the very idea to use locale-specific characters in format string is bad. Besides being not very obvious and leading to this kind of mistakes, there is another issue: imagine having a format string of a cell like "# ##0.00". If I have the LO default locale set to English (USA), and the cell locale is default, then the space has no special meaning, and should be treated as textual insert. The number 1000000000 is displayed as 1000000 000.00 . If then I change the LO default locale to Russian (or send the file to a user having this locale), the space becomes standard thousands separator. LO modifies the format string representation to be "# ##0,00" (note dot changed to comma), and the format string is expected to make 1000000000 look like 1 000 000 000,00 (at least that is what I get when I enter this fmt string diretly in a Russian-locale cell). But in this previously-English cell, this format string will continue to treat the space as textual character, giving 1000000 000,00 - and that is absolutely unexpected to user looking at the format string!

It would be better if the separator characters of format string were fixed, and inserting them as text would always require quoting them. It would make the format string unambiguous.

Marking as NEW per comment 16

Major - loss of data/formatting (silently lost)
Medium

Still reproducible with Version: 4.4.3.2
Build ID: 88805f81e9fe61362df02b9941de8e38a9b5fd16
Locale: ru_RU
under Win7x64

Does not occur anymore (at least since 5.1.5)