https://bugs.documentfoundation.org/show_bug.cgi?id=153281
Bug ID: 153281
Summary: AutoConvert inconsistent choice between TEXT, DATE,
NUMBER and FRACTION cell format.
Product: LibreOffice
Version: 7.4.4.2 release
Hardware: x86-64 (AMD64)
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Description:
When introducing a number, a cell is automatically formatted as DATE while the
value is treated as TEXT (not as date nor as fraction). This is inconsistent
and not intuitive.
If I wanted it as text, I would use an apostrophe.
Note that the typed-in "1/4" is AutoConverted; now its LEN() is not 3 or more
characters, but 1 (as in the UNICODE character 188 = U+00BC "Vulgar Fraction
One Fourth").
Note that the cell's Format is DATE (not TEXT nor FRACTION); before typing-in,
the format of the cell was General.
Because of AutoConvert, the content of this cell is displayed as a one-length
TEXT character that looks as a fraction, the cell gets AutoConverted as DATE
and there is no equivalent internal serial number for such DATE (whether it
would had been 1st of April xor 4th of January of some year).
I could understand if AutoConvert would consider this "1/4" (without quotation
marks) as either:
_ a TEXT value displayed as UNICODE character 188 = U+00BC "Vulgar Fraction One
Fourth"; or,
_ a NUMBER value of 0.25 displayed as a FRACTION of 1/4; or,
_ a NUMBER value of 45017 displayed as a DATE for the 1st of April of the 2023
(current year ATM); or,
_ a NUMBER value of 44652 displayed as a DATE for the latest past 1st of April
of 2022; or,
_ a NUMBER value of 44930 displayed as a DATE for the 4th of January of 2023;
or,
_ a NUMBER value of 44565 displayed as a DATE for the 4th of January of 2022.
But, AutoConvert is evaluating this "1/4" (without quotation marks) and
converting it to a TEXT while setting the cell's format as a DATE.
In my experience...
_ If I have a DATE, I also have a corresponding internal serial number, which
can be used in formulas.
_ If I want to introduce a number (say, "1") as TEXT (not as number), I need to
either set the format beforehand, or add a single apostrophe before the number.
When AutoConvert can help the user, it is welcome.
IMO, when there is some ambivalence, AutoConvert should not automatically
assume that the user wants some specific formatting, unless the user can
configure it to do so (e.g. AutoConvert should not automatically give
precedence to dates vs fraction, or vice versa, until the user defines such
precedence, at least the first time). I could understand that someone con have
a different opinion, such as "let's provide a default behavior, and give users
the possibility to modify such order of precedence / assumption"). In any case,
the UI/UX should be intuitive, for users to be aware of these possibilities.
What I (really, sincerely) cannot understand is this mix, as the current
example with "1/4" shows. I am introducing a number, which could potentially be
either a FRACTION or DATE. If I wanted it to be treated as a TEXT, I would need
to introduce it with an apostrophe before it (as I would do with any other
number that I wanted treated as text).
The current behavior seems to be the worst of all worlds: I introduce a number,
then it is AutoConverted and the cell's format claims it is a date, it is
displayed as text, functions treat it as text, and the behavior of each of
these is not intuitive. These seem to be the result of subjective assumptions,
instead of consistent behavior and methods (which could/should be modified by
options available to the user, while the defaults should be wisely considered
with consistency and methodology in mind).
FWIW, for this cell, originally formatted as General, AutoConverted as DATE
once I typed-in "1/4" (without quotation marks), the following are the results
of some functions I used (with the specific cell as their argument):
UNICODE(): 188 (a text character)
TYPE(): 2 (i.e. text, not date/number)
N(): 0 (zero, thus, as if the value was either zero or text, not 0.25 nor the
internal serial number for the interpreted date)
LEN(): 1 (as text; what about this being a DATE, either 1st of April or 4th of
January of some year?)
Steps to Reproduce:
1. Start/Open new Calc.
2. Type in (without quotation marks) "1/4" [enter].
3. Check cell's format and apply some functions with unexpected results.
Actual Results:
I introduce a number, 1/4. The value is Autoconverted to TEXT, while the format
is converted from General to DATE. Functions treat it as TEXT.
Expected Results:
The value is not treated as FRACTION (a number format), nor as DATE (also a
number) but as TEXT. If I wanted TEXT, I would had used an apostrophe.
AutoConvert should have consistent behavior and relevant intuitive options
available for the user.
Reproducible: Always
User Profile Reset: No
Additional Info:
I tested this using LO 7.4.4. In a few days I will be updating to 7.4.5. If
this behavior is different (i.e. more consistent) in 7.4.5, I will update
sooner.
With so many (bug) reports regarding fractions and dates and such, there has to
be a way to make this behavior consistent, rather than changing it according to
completely subjective preferences, particular usage, specific cultural habit,
or lack of awareness of methods from the part of users.
Version: 7.4.4.2 (x64) / LibreOffice Community
Build ID: 85569322deea74ec9134968a29af2df5663baa21
CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: default; VCL: win
Locale: en-US (es_AR); UI: en-US
Calc: CL
--
You are receiving this mail because:
You are the assignee for the bug.
