Re: [libreoffice-users] Date/time autocorrection (update me)

At 00:17 08/06/2016 -0500, Edwar Cifuentes wrote:
> I've been trying to find a way to disable (as in one definite 
> action, not a repetitive action performed every time it's needed) 
> calc from turning things like 2/2/2 into 02/02/02.

It's in the nature of spreadsheets that values that you enter are 
often interpreted in some way or other: you are not simply typing 
into a cell. If you type "false", the value stored in the cell is a 
boolean value and the display shows "FALSE", not "false". If you type 
"1E3", the value stored is a thousand, but the display shows 
"1.00E+003". In addition, the cell formatting is changed to 
Scientific, so that if you subsequently type "123" into the same 
cell, the display will show "1.23E+002". You can follow some of these 
effects (but not all) by observing the difference between what 
appears in a cell and what appears in the Input Line when the cell is 
selected - often different.

If you don't want this sort of assistance at all, you may not want to 
use a spreadsheet. (But read on.)

> List of things I've found and tested so far without satisfaction:
> Use apostrophe before data: '2/2/2
> works but has to be done per each cell so doesn't really count as 
> disabling like I said above.

As you imply, this is ideal for occasional departures from normal 
behaviour - and you should make use of this when required.

> Format cells as "text"
> works but you have to do it before entering the data, or else you'll 
> lose the data, ...

This is the Right Answer to your question. Generally speaking, you 
should know before you enter data what you want to do with it and 
should format cell ranges (rows, columns, or whatever) appropriately. 
If you want what you type to appear just as you entered it, you will 
indeed want to format the relevant cell ranges as Text before you 
enter data. (Note that you can select large areas and format them in 
one action - even an entire sheet.) Surely that is no burden? Indeed, 
it may force you to think about exactly what you intend to do with 
the spreadsheet - which is a Good Thing!

> ... which after you apply the text format to the cell, will be 
> converted to another inexplicable number. Example: I type 2/2/2, it 
> gets converted to 02/02/02, then I change the cell's format to Text 
> and then I see the number 37289?...

There is nothing inexplicable about this. If your entry "2/2/2" is 
just that - a five-character string with no other meaning - then you 
will have formatted the column (or whatever) as Text and you will get 
and see what you want. If it *is* actually a date, you should want 
the value to be interpreted as meaning 2 February 2002 and you should 
format the cell range to display it as you wish. The format string 
"D/M/YY" (or "M/D/YY", depending on your locale) will show "2/2/02" - 
which is close to what you want. It is only by allowing this to 
happen that you will be able to calculate with your date; calculation 
is surely the usual point of using a spreadsheet.

Dates are stored internally as the number of days from a starting 
date, which is 30 December 1899 by default. Your date, 2 February 
2002, is simply 37,289 days after 30 December 1899. Note that this 
means that your "2/2/2" is not "turned into 02/0/202" but into the 
number 37289; the cell formatting (also set automatically) is then 
responsible for the exact details of the display.

> Additionally, sometimes you're entering data on the fly, or making a 
> sketch, and you don't really have defined where text only data will go, etc.

If you *really* don't know what you will be doing until you do it, 
you need to use the apostrophe technique or to format individual 
cells as Text as you go.

(I've excised your other suggestions since, as you say, they are irrelevant.)

This shouldn't ever happen, but if you do enter a lot of data and it 
gets interpreted in ways that you don't want, you can (nearly?) 
always use the functionality of a spreadsheet to put things back as 
you want them. Taking your fake date as an example, you could use the 
TEXT() function along with the "D/M/YY" (or "M/D/YY") format to 
reconstruct "2/2/02" and then one or more of the string functions to 
remove the unwanted zero. But if you have a clear idea before you 
enter data of what you are doing, you will not need to do this.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted