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.
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.
To unsubscribe e-mail to: email@example.com
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy