Description: In calc: A number format code which does not specify any digits to the left of the decimal for example ".00;-.00", is spontaneously changed when saving/reloading to insert a '#' before the decimal, to ""#.00;-#.00" in this example. The problem is that until a save/reload is done, the format mis-behaves: The '-' symbol is mis-placed in large negative values. For example "9999-.00" is shown instead of "-9999.00". I think LO should either: 1. Handle ".00;-.00" correctly (i.e. not mis-place the minus sign) and do not alter the format when saving; or 2. Immediately force-insert the '#' instead of waiting for a Save/Reload (or treat the format as an error and inform the user). Either way, LO should not display wrong results, or different results after save/reload. Steps to Reproduce: 1. Right-click on a cell -> Format Cells -> Numbers 2. Enter ".00;-.00" (without quotes) in the "Format code" box. Click OK 3. Enter a large negative value into the cell such as -99999 (wrong display appears) 4. Save & Reload (display is correct; inspecting the format shows it was changed) Actual Results: Display shows "99999-.00" Expected Results: It should show "-99999.00" and should not modify the format during save/reload; OR, the format should be modified instantly when the user enters it so it will work; OR, the user should be informed that the format is illegal (if it is...) Reproducible: Always User Profile Reset: No Additional Info: Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 3b73071f7a7fcf80547da81e5effe4ed6018bbb4 CPU threads: 12; OS: Linux 6.5; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded
While I can confirm the behavior, I must also say that using ".00;-.00" (without quotes) is not an accepted way to provide a number format. <https://help.libreoffice.org/latest/en-US/text/shared/01/05020301.html> If we use the fields: Decimal places: 2 Leading zeroes: 0 we already get "#.00", as expected. Checking "Negative numbers in red" will give: #.00;[RED]-#.00 from which we can remove the red to obtain: #.00;-#.00 which shows the correct result. Additionally, when entering the proposed ".00;-.00" (and before clicking on OK accepting this format), the preview area (to the right side of the number format dialogue) will show the unwanted "99999-.00". So, I will set this as a new enhancement request, but I would say this has low priority too (I am leaving this as normal; QA members might change it).
I think this is a bug because the documentation nowhere mentions that a digit symbol must precede the decimal, and in fact there is an EXAMPLE in the docs of a format without anything before the decimal (although it does not involve negatives). The format string docs are at https://help.libreoffice.org/latest/en-US/text/shared/01/05020301.html?&DbPAR=CALC&System=UNIX The counter-example (sort of) is «.#,, "Million"» at https://help.libreoffice.org/latest/en-US/text/scalc/guide/format_value_userdef.html?DbPAR=CALC#bm_id3143268 FWIW, in the real application I had to hand-type the format string because the Format Cells dialog options do not support what was needed (percentages with different colors for both positive & negative values). I probably omitted any leading '#' in a mistaken attempt to allow the column to be narrower when all values were < 1.00%
Created attachment 193579 [details] XLSX file with ".##;-##" format The bug appears also when opening an XLSX file containing format: .##;-.##