disable 2-digit options

classic Classic list List threaded Threaded
3 messages Options
Thomas Blasejewicz-3 Thomas Blasejewicz-3
Reply | Threaded
Open this post in threaded view
|

disable 2-digit options

Good evening
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 to set the desired font for all sheets.
Problem:
I DID set a certain "date format"
and
a certain "number format".

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

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.

In LibreOffice, years are indicated by four digits, so that the
difference between 1/1/99 and 1/1/01 is two years. This *Year (two
digits)* setting allows the user to define the years in which two-digit
dates are added to 2000. To illustrate, if you specify a date of 1/1/30
or later, the entry "1/1/20" is recognized as 1/1/2020 instead of 1/1/1920.

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.
And, naturally, having to format those columns by hand, for all sheets
(and maybe all eternity)
AND ... my patients turn out to have a VERY strange "date of birth":
70 ("age" translates into ) 1900/3/10
25                                   1900/1/24
33                                   1900/2/1    (I am sure, there is
some logical rule, but I cannot see it)
The above cited help section does NOT indicate any way of how to
*DISABLE *that function.
Is there a way to do so?
I tried to just delete the number in the year field. THAT did not work.
Any help would be greatly appreciated.

Thank you.
Thomas


--
To unsubscribe e-mail to: [hidden email]
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
Luuk Luuk
Reply | Threaded
Open this post in threaded view
|

Re: disable 2-digit options


On 29-12-2018 15:18, Thomas Blasejewicz wrote:

> Good evening
> 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 to set the desired font for all sheets.
> Problem:
> I DID set a certain "date format"
> and
> a certain "number format".
>
> However, this gives me either an incomprehensible 5-digit number for
> the dates (of birth),
> or alternatively a similarily incomprehensible 5-digit number for the
> age = 2 digits.
>
> 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.
>
> In LibreOffice, years are indicated by four digits, so that the
> difference between 1/1/99 and 1/1/01 is two years. This *Year (two
> digits)* setting allows the user to define the years in which
> two-digit dates are added to 2000. To illustrate, if you specify a
> date of 1/1/30 or later, the entry "1/1/20" is recognized as 1/1/2020
> instead of 1/1/1920.
>
> 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.
> And, naturally, having to format those columns by hand, for all sheets
> (and maybe all eternity)
> AND ... my patients turn out to have a VERY strange "date of birth":
> 70 ("age" translates into ) 1900/3/10
> 25                                   1900/1/24
> 33                                   1900/2/1    (I am sure, there is
> some logical rule, but I cannot see it)
> The above cited help section does NOT indicate any way of how to
> *DISABLE *that function.
> Is there a way to do so?
> I tried to just delete the number in the year field. THAT did not work.
> Any help would be greatly appreciated.
>
> Thank you.
> Thomas
>
>

A date is kept, internally, as a number.

So if a cell contains 33, nd is displayed as a date, you will get
'1900/2/1', which is 33 days after counting the days started.


BTW: Why are you storing age ? Next year all your patients are one year
older. Is it not much better to just store birthdates?




--
To unsubscribe e-mail to: [hidden email]
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
Windows7 / LibreOffice  3.3 330m19(Build:6)
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: disable 2-digit options

In reply to this post by Thomas Blasejewicz-3
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: [hidden email]
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