Calc: How to Prevent Reformatting?

[MarjaE]

I have had trouble with spreadsheet software taking the entries and converting the entries into gibberish. Especially when converting from other formats into LibreOffice.

For example, if one column includes two sets of numbers A/B or three sets A-B-C, it may misread them as a fraction and convert them to decimals, or it may misread them as a date.

I think I can prevent this by defining all cells as text, but I dn’t remember how to define all cells as text.

P.S. I also need some sortable dates in this spreadsheet. How can I do this without worsening my rsi and without any confusion betwee day and month? I use either names or Roman numerals in text, but that may not work in sortable dates.

[Lupp]

(No idea what an “rsi” might be.)
Unfortunately there are so called recognition routines applied to entries made into cells in excess of what actually is necessary.
What we have is a partly inconsistent mix of software behaviour allegedly best adapted to user expectations.
Most unfortunately these expectations differ relevant from region to region and software is doomed to try to resolve this by establishing some 100 locales eg.
Unfortunately? Maybe the “state of the art” is rather a consequence of laziness and unawareness of consequences.

DATES
The standard representation of dates is numeric. If you are using it you may have displayed (formatted) the dates in whatever way you want. The underlying values will not be changed by that and the dates are sortable.
If there is need to import or to enter/keep dates as text the real problems arise, mainly because most users will not accept to use the well specified international standard. The one and only standard of writing dates in a human readable form also acceptable for spreadsheets is YYYY-MM-DD (4-digit-year!). If you use it in a cell applying the recognition process of any locale it will be correctly interpreted (despite the fact that it will, in most locales, be incorrectly displayed if you not rectified the cell’s number format). Using it in cells bound to not apply the recognition (fake number format ‘Text’ / ‘@’) you will have dates in text form which are sortable without a problem. In addition the ISO8601 conforming dates as described are unambiguously convertible into their numeric equivalent at any time.
Using the minus sign in A-B-C group with three numbers can, of course, only reliably return a correct date value if it strictly obeys above scheme. In every different version the result may be something else depending on settings.
A/B on the other hand is a fraction, isn’t it? How would you write fractions otherwise? Unfortunately there again are settings forcing the software to forget that and to interpret such an entry as a date automatically complementing it with the current year. Fortunately, for some time now already, LibO Clac allows for switching off such misleading “recognition” via the ‘Date acceptance patterns’ control in > ‘Tools’ > ‘Options’ > ‘Language Settings’ > ‘Languages’.

OTHER RECOGNITION ISSUES - an endless story.

Upshot: We should admit that a spreadsheet will only convert gibberish into (a different) gibberish. Unfortunately gibberish seems to be our fate.

[AlexKemp]

RSI == Repetitive strain injury. I first met the term RSI in connection with typists of the 1960s / 1970s who were paid by the word; the more they typed, the more they got paid, the worse the injury to the soft tissues of their hands. That then became universal with GUIs & mice. Almost everyone that uses a mouse gets RSI.

[frustrationwasmyname]

Install Gnumeric! (if you are on linux). I signed up especially to add this answer. After endless frustration with LO mangling every date I ever entered in the most inane and thoughtless ways I installed gnumeric. I can paste a large copy&paste from a database query with dates like: “2015-11-03 12:15:05” and in my spreadsheet I get… you guessed exactly the same. Thank goodness! I need to flip back and forth from spreadsheet to text file date formats constrained by other software, and so date mangling is a show-stopper for me. LO you lost me, unless and until I need these bloated auto features.