Re: [libreoffice-users] disable 2-digit options

At 23:18 29/12/2018 +0900, Thomas Blasejewicz wrote:
> I am using Calc to maintain a list of patients including entries 
> like name, age, date of birth etc. I tried to set a consistent style 
> to about 20 different sheets. Sheet styles do not allow to adjust font etc.

True.

> I choose character style ...

I think you mean a cell style?

> ... to set the desired font for all sheets.

That's inappropriate, if you want the same font everywhere. Why not 
just use local formatting?
o Select all your sheets at once using click and Shift+click on the 
tabs at the bottom of the first and last sheets.
o Click in the small rectangle at the top left of the visible sheet - 
where the row and column headers intersect - to select the entire 
sheet. Since you have previously selected all the sheets, this will 
select the entire sheet of all sheets.
o Apply whatever formatting you wish to apply everywhere - such as 
your chosen font.
o You can still apply different formatting to parts of your sheets 
later, of course - perhaps headings and so on.

> Problem: I DID set a certain "date format" and a certain "number format".

No, you cannot do that: any cell style (and you appear to be creating 
only one) has only one choice for Numbers, and this can be Number or 
Date (or other things), but not both.

> However, this gives me either an incomprehensible 5-digit number for 
> the dates (of birth), or alternatively a similarly incomprehensible 
> 5-digit number for the age = 2 digits.

Well, I'm not surprised! You cannot expect one cell style to provide 
multiple purposes. You have clearly chosen a Number format, not a 
date one. The effect of this on stored dates is to suppress the 
normal, useful display and instead show the internal number stored 
for each date, counted in days since the base date. For example, 
today is 43464 after the (default) base date, so that is the number 
you will see. If you don't want this, the solution is easy: format 
your dates as Date, not as Number.

Incidentally, I'm not sure you realise that, when you enter into a 
cell something that Calc recognises as a date, the cell format is 
automatically set to Date so as to display the value usefully. You 
have upset this helpful process by setting the format to Number 
instead. Understanding that sort of thing is fundamental to knowing 
how spreadsheets work and how you use them.

> I found under: Tools - Options - General -> a "Two digit options":
> Year (two digits)
> Defines a date range, within which the system recognizes a two-digit year.

This is nothing to do with cell formatting. Instead, it controls what 
happens if you enter a date without specifying a century. If, for 
example, I enter "30/12/18" (in my locale), that "18" is - sensibly 
and usefully - interpreted as 2018, not 1918 or 2118 or anything 
else. Note that the display in the cell may well have just "18" for 
the year, but the value stored internally is unambiguously 2018. But 
what if I enter "1/1/50": should that be interpreted as 1 January 
1950 or 2050? You can control the interpretation of such two-digit 
years in the Options dialogue you describe. Indeed, with your dates 
being dates of birth, you may want to set this to something like 1921 
to 2020; you may have dates of birth in the 1920s but it is unlikely 
that you will have any in the 2020s!

> That may be wonderful in certain situations, but leaves me having to 
> choose between the "age" (MOST of my patients have a 2-digit age!!!) 
> or "date of birth" of my patients.

No: it simply requires you to create separate cell styles (if that is 
what you need) for different parts of your data - specifically, 
separately for dates and ages.

> And, naturally, having to format those columns by hand, for all 
> sheets (and maybe all eternity) ...

Create one sheet. Insert headers as appropriate. Apply styles and 
local formatting as appropriate. Right-click the sheet tab and select 
Move/Copy Sheet... to copy the sheet as many times as desired. If you 
have already filled your sheets with data, the procedure is just as 
easy: copy a sheet and then simply delete the existing data - either 
using Backspace or taking care not to delete cell formatting.

> ... AND ... my patients turn out to have a VERY strange "date of 
> birth": 70 ("age" translates into) 1900/3/10

You seem to be expecting one value to express age and date of birth! 
No, as someone has already said, this is just what you see if you - 
foolishly - try to format an age value as a date, which it is not.

> (I am sure, there is some logical rule, but I cannot see it)

Seventy days after the (default) base date is 10 March 1900.

> The above cited help section does NOT indicate any way of how to 
> *DISABLE* that function. Is there a way to do so?

Just set the cell format of each cell, column, or other range (using 
cell styles or local formatting) to what it needs to be - Number or 
Date, as appropriate.

> I tried to just delete the number in the year field. THAT did not work.

That depends on how you choose to delete the contents of the cell. If 
you use Backspace, you indeed delete the value but retain the cell 
formatting. If you use Delete or Edit | Delete Content... (or 
right-click | Delete Contents...), you can opt what to delete - 
specifically, whether or not you delete the cell formatting.

Incidentally, you appear to be applying cell formatting after you 
have entered values, which is unwise and ineffective. Set appropriate 
cell formats where necessary *before* entering data.

As someone else has suggested, storing ages is perhaps suspect, as 
these have been known to change! Why not calculate these in the 
spreadsheet from the date of birth and the current date? They will 
update by magic. If you don't have the date of birth, you would at 
the very least need to save the date on which each age value was 
harvested and known to be correct.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy