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
I forgot 1 additional function. I also repeat here those that I posted before: 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?) CELL("FORMAT"): D1 (really? because the others claim it is text)
Note that the input of 1/4 getting converted to U+00BC ¼ happens already on editing level, hence the input the cell receives is already text. Whatever number format is applied to the cell does not matter at that stage. To prevent that from happening, disable Tools -> AutoCorrect Options..., tab Options, "Use replacement table"; or disable individual fraction replacements in the Replace tab's table. Also, if AutoCorrection was already applied, pressing Ctrl+Z once will undo that step and the original input of 1/4 will be re-fed to the cell, resulting in date. CELL("FORMAT") returns date because that is the cell's number format. All other functions you list work on the _content_ of the cell, which is of type Text.
(In reply to Eike Rathke from comment #2) > Note that the input of 1/4 getting converted to U+00BC ¼ happens already on > editing level, hence the input the cell receives is already text. Thank you for your reply. I still see this default behavior as non-intuitive and goes against other methods we have know for decades (e.g. forcing it as text by means of apostrophe). IOW, 2 inconsistencies, and very subjective default behavior. Users can't force each individual input to be treated as date - or as fraction. But each input can be forced to be treated as text. So choosing to force it to be a text by default reduces the available alternatives for users. It's a simple logical flowchart. When using AutoCorrect to force this input as text, the cell format should be text, not date. If Autocorrect is not enabled to force the input as text, then its OK for the cell format to be a date (and the input to be treated as such too). The current mix doesn't make sense to me, and certainly it is not intuitive or natural.