It has been introduced because too many users complained about 12/3 or 2:3 or 3-1 being interpreted as dates or times. Declaring these strings as text was too much for them. This was a problem for some users.
OpenOffice 3 introduced this new option and turned it off by default. SInce 12 years numeric strings imported from csv is the most frequently reported problem with Calc because this option is turned off and nobody understands what a special number is.
The solution to this problem would be: Import pairs of numbers separated by date delimiters as string. A date consist of 3 numbers. Handle keyboard input differently from text import.
The better -and the only good and safe- way to get rid of the mess would be to exclusively communicate (including via any variant of csv) dates and date-time values obeying the existing standards given by ISO 8601.
A conversion to a numeric representation as preferred for spreadsheets can then be done (accepting the arbitrary “null-date” of the version) by the receiving person if she is knowing what he does. This can be done during import or later using formulas or whatever.
Calc even allows to calculate with ISO-near textual representations without any explicit conversion.
See attached little rexample. disask77899calculationWithDateTime.ods (13.4 KB)
Implicit conversions should be used with great care. 
For example, guess without the help of Calc the result of the calculation of the formula:
=LEN(-"1:")
"1:" is an hour. Why it don’t work without the minus sign?
Only when converted to a number.
Because the Len parameter must be text.
1 Like
The given example =LEN(-"1:") does no longer work under V 7.3.3.2. It still worked “as expected” (by sokol92) under V 6.4.5. Now you need (e.g.) =LEN(-"1:0") to get the string accepted as a time and autocoverted respectively.
However, I wasn’t talking of absurd examples but of purposeful ones, and if you make sure to pass (basically) ISO 8601, the conversion is reliable (Zone info currently excluded)…
If the purpose, however, is to prove the absurities of the spreadsheet-Do, you can easily get any formula result (except an error message) to be shown as “I am wrong!”.
Only opinions now:
Far beyond the examples, I generally am doubting automatic conversion if not strictly and restrictively specified for any kind of a propgramming system. The shortest way to give a specification of the kind is to exclude strings generally, and to allow number conversion only “upward” to the more general type, only if there is a single chain of types, and only if the conversion always is done in one step by native means. Good old Pascal offers the usage of the type name as the name of a function converting to that target type.
We also have partly a mix-up of automatic conversion and of overloading operators or functions/routines/methods concerning their parameter types. When I studied programming a bit more seriously nearly 60 yeras ago there was no overloading and rarely auto-conversion. I didn’t miss them. But there was a starting uglyness pretending to be due to efficiency, ruled by FORTRAN, and still present in …
I expected and see 19 (length of "-0,0416666666666667"). By the way, in Excel you get the same result.
Version: 7.3.4.2 (x64) / LibreOffice Community
Build ID: 728fec16bd5f605073805c3c9e7c4212a0120dc5
CPU threads: 6; OS: Windows 10.0 Build 19044; UI render: default; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: threaded
Version: 7.3.3.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 5.13; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.3~rc2-0ubuntu0.20.04.1~lo1
Calc: threaded
The main problem with implicit conversions in Calc (and Excel) formulas is that the conversion rules are locale dependent (as you yourself have pointed out many times)…
Suprise. I actually tested =LEN(-"1:") in V 7.3.3.2 (Win 64) and got #VALUE!.
ISO 8601 is not locale dependent, and therefore what I recommend to exclusively use everywhere.
From my point of view the first main problem is bad teaching. To give an example: There is an officious specification in Germany by DIN 5008 for business letters, containing as a norm the ISO 8601 date-format (DIN 5008 – Wikipedia). Trained typists simply don’t know it, and regard its usage a crackpot idea. If I need to verify my credit card data via phone, I’m forced to use an idiotic 6-digit-format for my birthday: DDMMJJ (JJ in place of YY). Being 100+ years: How to?
The second main problem concerning spreadsheets specifically is the “compatibility with Excel”. I simply can’t be compatible with the results. It would require to be “as crazy as MS likes me to be”.
1 Like
I wish this was our problem!
Trying to get back on topic: When Calc opens the csv with timestamps as text, it widens the column from 2,26 cm to 3,63 cm which is wide enough. When Calc imports the correct values, it widens the column to 3,28 cm which is not wide enough. The displayed cell string is the same for both types text and formatted number. My default font is Liberation Sans 10pt.
Depends on Tools → Options → Calc → Formula, Detailed Calculation Settings, if Custom Details Conversion from text to number is set to Generate #VALUE! error it will result in exactly that, a #VALUE! error in E2:E3. Which is the only sane option, but too convoluted for Excel users and documents, because Excel even does the worst of all, the locale dependent conversion.
2 Likes
The expression -"1:" depends on Calculation Settings, Conversion from text to number.
Numeric is implicitly converted to text. =LEN(3/2) results in 3 (length of text 1.5).
1 Like
Good to now your informed opinion. My setting there is Convert only if unambiguos, and I thought it was safe (sane) enough. Of course this depends on how the “unambiguous” is judged. What are your main concerns?
A background of my own considerations is my strong preference for ISO date and time strings - and ISO 8601 definitely specifies a textual representation of date-time. This said, I would prefer it over any representation of dates by numbers because it may be (next to) locale independent, but surely depends on an arbitrary null-date.
To recommend textual date-time representation in sheets, on the other hand may be too unhandy if explicit conversion is needed in every case of intended calculations. (There isn’t a function DATETIMEVALUE() afaik, and i need to use two functions for one conversion of a date-time-stamp.)
That any implicit conversion, even unambiguous, may hide conceptual or data errors, where for example number sequence arguments like SUM() and its family members expect ignore all text content cells but operator + calculations implicitly convert such operand.
What is a typist?
No, it’s for validation of birthday. 100+ is no problem, calculating birth year is.
In the real world, yes.
Um, maybe I’m misreading what you’re saying, but users complaining about 12/3 or 2:3 or 3-1 being interpreted as dates or times would be addressed by the values just showing as text; the users asking for them to just be displayed as text doesn’t seem like the problem, it looks like the solution.
Declaring them as text means right-clicking the respective column headers in the import dialog and choosing respective type, which is too much trouble for most (and which accidentally answers your “did I just miss the option “display all values as-is text”.
It may be related to typography.
PAIR KERNING affects LO’s interpretation of optimum column width.
Bug147015 may provide insight into a possible solution.
I’m not sure if you can pre-define the characteristics of the target sheet for a CSV import - one of the “Big Boys” may be able to provide more help.
Added: If you are importing CSV into an established sheet then importing into the existing format of the receiving sheet should stick the data into a pre-defined PAIR KERNED column.
My original caution concerning the CSV import was related to simply opening a CSV file in LO
Looking at your sample data the predominance of 00:00 and all the 0s in the other values would affect LO’s perception of the “average” kerning values in the text. I suspect the optimum column width is relying upon an interpretation of the average pixels for say 19 characters as opposed to the actual pixel count of the “longest” value.