Date: prev next · Thread: first prev next last
2018 Archives by date, by thread · List index

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.

Brian Barker

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: 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.