Format Cell as Date - LibreOffice

[Alex in Grimsby]

Let me immediately apologise for posting a query about Libre Office in this forum. I will happily accept and welcome correction by anyone here I am only using LibreOffice temporarily until I can cure my problem with OpenOffice (see my post elsewhere on here Error n1304 and 1310 …). I have failed to find the proper place to ask this brief query, which is, why does LO's format cell as date not work? It just ignores repeated attempts to format as a date.

[robleyd]

We support LibreOffice here - see the nearly invisible white text in the header above to the right of the logo.

Perhaps the cell content is actually text; if so changing the format won't affect the display of the contents. Use View | Value Highlighting or Ctrl + F8 - text cells are formatted in black, formulas in green, and number cells in blue, no matter how their display is formatted.

[Villeroy]

LO behaves more or less like any other spreadsheet. If your cell value is a text actually, no formatting will ever change this matter of fact and no number formatting applies to text. The text "00123" is not a number. Likewise the text "1/13/1999" is not a date.
Menu:View>Highlight Values [Ctr+F8] highlights constant numbers in blue font. Dates in spreadsheets should be formatted day numbers.
[Tutorial] Ten concepts that every Calc user should know

How to avoid date strings depends how these values got into your sheet. Copy/Paste? Text import? Formulas? Manual entry?
How to fix date strings depends on the locale setting in Tools>Options>LanguageSettings>Languages>Locale

[Alex in Grimsby]

Thank you RobleyD and Villeroy for your quick replies. I have been using computers and spreadsheets for many years, but nevertheless, whilst I 'get' what you both have explained, it still doesn't quite tie in with what I'm used to in OpenOffice. In OO the Format option offers numbers, date, currency, etc etc. So depending on which style of date I have selected, (07/11/2020, 7th November 2020, etc.), if I type in 7.11.20 OO will then display it as 07/11/2020, 07.11.2020, 7th November 2020, 7th November 20, etc. In other words, I can type in a very abbreviated form and OO will expand it for me.
I still think there is a fault here as I have just tested the formatting option as follows - in every case with zero response/change from LO. I clicked inside a cell containing 20.11.2020. I right-clicked it, selected Percent, format 13% and clicked ok. No change. Next I tried doing the same with the same cell but using the Format menu at the top of the sheet and selecting Percent. Still not. Just in case it is those figures it doesn't like, I changed that same cell to the first part of the date, so 20.11 and repeated that last effort. Still nothing at all. So far I'm just hoping that I can get my OO query elsewhere on here cured ASAP!

[Villeroy]

If the German date 20.11.2020 is a text, no formatting atribute will convert this string into a number. If it is German day number and you want this exact same value be displayed as an English date, you should change the language option in the number format dialog. English(USA) displays the same value as 11/20/2020 and any other English setting will show 20/11/2020. The default language for numbers all across the office suite is the above mentioned locale option in the language settings.

If value highlighing shows 20.11.2020 in blue, it is a number. Black indicates a text. Likewise =ISNUMBER(A1) =ISTEXT(A1) or TYPE(A1) reveals the true type of value in cell A1. There are 2 simple methods to convert text to numbers. But first we need to know the actual type of value being text or number and the global locale setting in the your language settings. You can also upload the document in question.

Edit: Oh, I always forget about the nasty date input patterns in LO. This is meant to allow you "full contol" over valid date entries. You find this non-feature in the language settings.

[Alex in Grimsby]

Hi again Villeroy, Thank you again for the fast response. I'm not sure why you describe 20.11.2020 as a "German" date. That format is almost universally used here in the UK, (ie. day/month/year). I'm guessing, (correctly yes/no?), that you are in USA where month/day/year, or even year/month/day is preferred, I believe - but's it's certainly not the norm over here.
I definitely intended that to be a date, (hence my query in the first place!), so to slightly repeat myself, in OpenOffice I can for example simply type in 2.8.20, or maybe 2/8/20, or even 2 Aug 20, and OO will then 'correct' it to 02/08/2020, 2nd August 2020, 2nd Aug '20, etc. according to which format I have selected. Given that Libre Office is based on OO, and in many ways is virtually identical, perhaps not too unreasonably, expected it to be capable of doing the same. I certainly did NOT expect it to totally ignore commands! Thank you for offering the suggestion regarding changing the language option - as you explain it, that comment certainly makes sense. However, what does not make sense is that LO is not responding at all to my attempts to change the formatting of the cell, and even worse, is that in my Format Cells dialogue box, under the Language heading it says Default - English (UK). So that sadly is not the explanation, but I appreciate the suggestion anyway. My 40-odd years' experience of using computers tells me that programs can often do quirky, illogical things. So with that in mind, I know that it is possible for one cell to misbehave whilst the rest do as they are told, impossible though it is in theory, so I have just tried to format a different cell, about a dozen columns and ten rows away from the the naughty one. No luck, same cold shoulder treatment

[Alex in Grimsby]

Villeroy - thank you also for your comments regarding value highlighting and that link to the Tutorial. I HAVE looked at the latter. Under the section "3. Dates in cells" it says: "if you pick English (UK) dates are DD/MM/YY." That is exactly what I'm after, (or even variations on that, eg. 2nd October 2020. LO just will not respond. Regarding Value Highlighting, another thing my many years of use has given me is the ability to understand that you are never too old to learn, so I will cheerfully tell you that I've never heard of this, never mind use it. More to the point, I have never NEEDED to know about it with OO. Whilst I can see that such highlighting could be handy, to date, OO has done all that I need it to do, including formatting cells in the great variety of ways it offers, without the use of that feature, (which I presume it also possesses.

[RoryOF]

In Calc, menu item /View /Value Highlighting is the very first debugging tool to use. Many Calc problems are caused by incorrect data types, and Value Highlighting shows these up.

[Villeroy]

The difference between text and numbers is the No 1 issue for all spreadsheet users since the 1980ies. You find these discussions in Excel forums of the past 30 years. Almost every day some other user struggles with wrong data, wrong calculation results, alphabetical sort order instead of sorting by value because people do not know (and therefore do not care) about data types. MS Excel makes things even worse when it guesses the numeric value of some numeric strings.

Until now, we still don't know:
How did these dates get into the sheet? Typing, pasting or via text import?
What is your locale setting shown in the global options and/or shown in the cell format dialog?
Are you struggling with text dates or with numeric German dates?

[Alex in Grimsby]

Well I must have been extremely lucky with my use of Open Office - I have never had any real issues in using it. It just does what I set it to do!
As for type of entry, just plain old fashioned typing directly into the spreadsheet - either directly into the cell or into the empty white bar across the top immediately below the text format icons, Bold, Italic etc.

[Villeroy]

So you type 20.11.2020 into some cell and the result is what? Text? Number?
I guess, it is a text and used to be a number with AOO.

Call Tools>Options [LO shortcut: Alt+F12]
and set the date acceptance pattern in the language setting to

Code: Select all

D/;D/M;D/M/;D/M/Y

This will give the date input behaviour as in AOO. Because of too many complaints by totally igorant "users", this date acceptance pattern has been introduced so they can do whatever they want.
Now LO Calc behaves like any other spreadsheet application
23/ enters this month's 23rd day
23/4 or 23/4/ enters this year's 23rd April
23/4/99 or 23/4/1999 enters the 23rd of April 1999.
In addition input according to the ISO standard 1999-4-23 (year-month-day) will be accepted.

[Villeroy]

What do you think is easier? Making your beloved OpenOffice zombie walk on your system or adjust LO to behave as you want it to behave? Some LO users persistently complained about normal spreadsheet behaviour and they were served with a stupid extra setting. As a consequence some other group of users needs to adjust either their habit of date entry or adjust the configuration to behave normally again.