Re: [libreoffice-users] Calc cell formats

At 19:38 02/10/2015 +0900, Thomas Blasejewicz wrote:
> I do have (naturally) a number of spread sheets.
> I did create a page style, where I tried to set "numbers" to 
> standard (because there are NO decimals!) and "date" to "1999/02/10".

o Spreadsheet page styles do not have cell formatting, so your 
attempts will have failed.
o If by "standard" you mean General (apologies if this is a locale or 
language difference), this does not limit numbers to having no 
fractional places. To do this, you need to select something like 
"-1234" (format code "0"). But General will display no fractional 
places for integral values.

> However, when I apply that style, ...

Remember that your page style does not affect cell formatting at all.

> ... the "date" which had been unintelligible 5-digit numbers changes 
> to the specified format, ...

They are not entirely unintelligible, though are not meant to be 
easily recognised. Dates (and date/times) are stored as the number of 
days (and fractions of a day) since the date origin. They appear as 
dates only when properly formatted. Remember that their appearance is 
entirely dependent on formatting and not the underlying data.

> ... but at the same time columns with the age of patients (TWO 
> digits, no calculations whatsoever) and consecutive numbers switch 
> to a row of three # =because they are too narrow to display the 
> "1900/02/10" inserted. What is this nonsense all about?

This is because you have (nonsensically?) chosen to format the age 
values as dates. Ages are not calendar dates, of course, so this was unwise.

> Why is the age of a person (e.g. 33) be replaced by a nonsense date?

You have chosen to format the relevant cells as dates. The internal 
value 33 will appear as the date 33 days after the date origin.

> Also, while I specified the date as given above, opening the very 
> same file under kubuntu, it suddenly is reverse to 02/10/19[00]. Why?

The operating system is irrelevant, I think. But what is relevant is 
the locale setting at Tools | Options... | Language Settings | 
Languages | Language of | Locale setting (though this may be 
inherited from the operating system).

> AND the style I specified does not seem to be retained. When I close 
> the file and reopen (in particular if this is on another computer) 
> it, the settings seem to have vanished.

I think the idea here is that the date value you exemplified - 
1900-02-01 - is indicated differently by default for users in 
different locales. In the everyday format, with the year last, the 
order of day and month needs to be appropriate for the user to 
understand it correctly. Otherwise, your example date, 10 February, 
may be read as 2 October. If you want date values to appear the same 
for all users, regardless of their locale (and with the risk that the 
dates will probably be misunderstood), I think you need to construct 
the representation yourself as a text string. You can do this very 
easily using the TEXT() function.

At 11:23 03/10/2015 +0900, Thomas Blasejewicz wrote:
> And HOW do I tell Calc (AND remember that setting!) that entering 
> simply "33" in a cell is NOT a date?

Simply by not choosing to format the relevant cells as Date.

> As I said: I tried to set numbers to standard and date to 
> "1999/01/10" format. The page style I tried to create allows ONLY 
> one OR the other ...

No: page styles do not affect cell formats, so cannot be used somehow 
to restrict cell formats the relevant pages may contain.

> I do not understand, why it has to convert all those simple numbers to dates.

It doesn't: it does this only because you ask it to. (And there's no 
"conversion", only different representation - which means that you 
can set everything right by correcting the cell format, without 
changing the underlying data.)

I trust this helps.

Brian Barker - privately


--
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