Bug 160319 - Calc: add constraints on Format Cells - Format Code
Summary: Calc: add constraints on Format Cells - Format Code
Status: RESOLVED WONTFIX
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
24.8.0.0 alpha0+
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: needsUXEval
Depends on:
Blocks: Number-Format
  Show dependency treegraph
 
Reported: 2024-03-22 17:06 UTC by Timur
Modified: 2024-04-15 13:58 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
MSO warning (25.51 KB, image/png)
2024-03-22 17:06 UTC, Timur
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Timur 2024-03-22 17:06:13 UTC
Created attachment 193247 [details]
MSO warning

This request is related to bug 95385. That one is more focused on Sidebar, this one is Enhancement to have warning.

If a user types wrong Format Code in Format Cell, data will show incorrectly.
Possible mistake is to type word from the wrong language, like Standard that is correct in German or French but incorrect in English where General is used .

LO should check typing in "Format Cells" and show a warning when typing something not good, as MSO does in screenshot attached.  

Maybe try to correctly translate "Standard" and other language variants to "General", currently we have hardcoded "general" ... (https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/numberformatsbuffer.cxx?r=ca060936#1835)
Comment 1 Stéphane Guillou (stragu) 2024-04-07 03:27:40 UTC
We'd have to define what a "wrong" format is. Users might use any number of combinations of characters for their custom formatting, and we'd risk breaking some workflow. Some might have a format that is specific to their business / systems, with extra literal characters, for which they'd appreciate LO's flexibility.

Can you describe what exactly would trigger such a warning?
Looking at the Number Format Codes documentation[1], I'm wondering if it's even doable to find a suitable set of rules. (No idea how MSO does it.)

Or maybe we just need to have a more obvious direct link to that documentation, instead of having to first go to the dialog's help page.
Comment 2 Timur 2024-04-08 09:52:41 UTC
Idea was to restrict just Format Code "General" description to the language of the cell, so to General for English Language (cannot type Standard), to Standard for German and French Language (cannot type General), etc.
Comment 3 ady 2024-04-08 15:26:48 UTC
FWIW, I never typed either "General" nor "Standard" in the Custom Code box. I either select the General/Standard item from the list, or simply delete the content of the Box and [ENTER] / click OK. When the content of the Custom Code Box is deleted and confirmed (by OK or [ENTER]), the resulting code is General/Standard.

@Timur, Perhaps this is not working in the same way in some languages (other than those I am used to use in spreadsheets)?
Comment 4 Heiko Tietze 2024-04-15 12:43:18 UTC
A valid number format is "Foo 000 Bar" producing "Foo 001 Bar" for the value 1. You may use one of the predefined formats such as Standard and even enter the name in the  The "Format Code" line but that's not how this control is supposed to work.

If the user enters "Foo Bar" in the format code, which obviously is not a reasonable format since neither #, 0, nor ? is part of the code we may warn. We also have to include DMY and HMS, AWQGER... all valid codes for date/time.

Is it really worth the effort and resulting in any meaningful warning?

See also https://help.libreoffice.org/24.2/en-US/text/shared/01/05020300.html and https://help.libreoffice.org/24.2/en-US/text/shared/01/05020301.html
Comment 5 Eike Rathke 2024-04-15 13:29:37 UTC
(In reply to ady from comment #3)
> FWIW, I never typed either "General" nor "Standard" in the Custom Code box.
Maybe you don't, but it's a valid keyword like all others. One can define for example

General;-General;;

to suppress 0-values.


(In reply to Heiko Tietze from comment #4)
> A valid number format is "Foo 000 Bar" producing "Foo 001 Bar" for the value
> 1.
Bad example..

Foo 000 Bar

is _not_ a valid format code, the trailing r is not accepted because R is a keyword in calendar date context (year in an implicit secondary (non-Gregorian) if defined calendar) and can't be mixed with a fixed numbers format like 000. Valid formats would be

Foo 000

only in a locale where neither F nor O nor OO are keywords (which luckily are all ;p only OOO and OOOO would be in some), or

"Foo "000" Bar"


> If the user enters "Foo Bar" in the format code, which obviously is not a
> reasonable format since neither #, 0, nor ? is part of the code we may warn.
Well,

Foo Bar

displays "Foo Ba2024" for a date in the year 2024 ... while nonsense in this example how would you like to distinguish a valid r年 in the ja-JP locale. You don't want to come up with a bunch of tweaked by per locale special cases.
Comment 6 Eike Rathke 2024-04-15 13:43:23 UTC
Btw, if one types in a format code relating to what can be seen in the attached dialog then already the preview shows it's certainly not what you wanted. Assuming German locale here, the format code

abgesagt: TT.MM.JJJJ

results in (for 2024-04-15) display of "abn. Chr.240an. Chr.15: 15.04.2024" and the format code when inspected again changed to

abGESaGT: TT.MM.JJJJ

so both are very obviously not what was intended.
Comment 7 Heiko Tietze 2024-04-15 13:58:59 UTC
Let's close as WF then.