My Libreoffice ODS spreadsheets seem to be locked into those goofy American calendar dates which make it hard to read and sort data by date. Or maybe they’re some kind of European format. Regardless, I can’t figure out how to reset a column of calendar dates to ISO format. It’s quite easy to do in OpenOffice, but LibreOffice has got me stumped.
1 Like
Please describe how do you do it in OpenOffice?
Format>Cells>Numbers>Date>“1999-12-31”. It works either by column or by cell. The result is proper ISO 8601 as required by statute… But on LO, nothing happens. The cells can be edited manually, but not formatted.
The same works for LibreOffice; and just as with OpenOffice, it only works if your data is dates, not text. So if your cells don’t follow the format you defined in cell properties, it only means your data need conversion from text to date - use e.g. Data->Text to Columns tool.
it hard to read and sort data by date
This is a clear indication that your dates are not dates (=integers) but text (as @mikekaganski already mentioned to be a probable cause of your issue). Sorting real dates doesn’t depend on the specific format.
The same set (i.e. column) of dates viewed in OpenOffice appears with the apostrophe flag at the beginning of the date as in “'02-02-2019”. (Look carefully and you can see the apostrophe just before the “02”). When this apostrophe is removed in OO using a reg-ex search and replace, the date text entries then instantly appear as usable dates, and can be manipulated like genuine dates. But LibreOffice (when viewing the same file) sees the date entries as plain text (no apostrophe flag), and nothing can be done. The CT2N extension doesn’t work, it simple says “Nothing to change”. Any suggestions?
@JamesJoseph: which locale is set in OpenOffice, and which is set in LO (see Options▸Language Settings▸Languages)? Both OpenOffice and LibreOffice import the dates as text, as you confirmed; but the apostrophe before the text in OpenOffice shows that the text is convertible to some value (date in this case) in current locale. Text Jan 5, 2020 is convertible to date e.g. in en-US locale, but not in ru-RU locale.
Additionally, the conversion can be performed using Data▸Text to Columns in-built function (no need to install anything external), as I already mentioned above. The tool allows you to define the imported dates’ parts order (e.g. DMY), to allow correct conversion.
MK: Both OO and LO are working with the same locale with the same “Date Acceptance Pattern: Y-M-D”, and OO’s dates are easily changed from one format to another. LO’s identical settings don’t yield a usable data format, only the fixed text format. OO and LO seem to have the identical import sequence, where text numbers are converted to usable numbers, etc., but OO’s import sequence doesn’t appear to touch the date data. I can’t see what the Data▸Text to Columns does.
I can put up a small sample ods file if it would help. It might be useful for others processing PayPal data of this kind with LO. Not sure where to load attachments - any ideas?
PS: I’m not totally stuck processing these files because it’s a piece of cake using OpenOffice. But I’m trying LibreOffice because I’m just curious about the new LO package, and it would be nice to get it working properly.
the same locale
… which? It might be a bug with the locale; could you please mention the details to avoid rounds of questions?
You may attach the file by editing your original question (there will be a clip toolbar button); or upload somewhere - like GDocs or something, and post a link here…
MK asks: “the same locale”?
This is all the locale info showing in Calc: “Locale Setting:Canada (English)” it also shows: “Date Acceptance Pattern: Y-M-D”
Here’s a sample piece showing non-ISO calendar dates used by PayPal:
01-01-2019 CAD $177.40 -$6.86 $170.54
03-01-2019 CAD $22.95 -$1.15 $21.80
11-01-2019 CAD $309.05 -$11.73 $297.32
12-01-2019 CAD $30.00 -$1.41 $28.59
19-01-2019 CAD $30.00 -$1.41 $28.59
22-01-2019 CAD $85.60 -$3.47 $82.13
26-01-2019 CAD $30.00 -$1.41 $28.59
The dates are fixed in text mode, and can’t be changed or formatted. In the real world, dates need only to be simple numbers for sequencing, that is “19980817” comes before “20190124”. Ideally, spreadsheets would offer this simple option instead of the incredibly complicated default method. However. I’ll take whatever I can get!
Is “01-01-2019 CAD $177.40 -$6.86 $170.54” in a single cell, or is it in several columns? (it would still be better to provide a sample spreadsheet with which you experiment, than to try to guess and keep this questions-and-answers game).
I just tested with AOO 4.1.5, setting it to en-CA locale, and couldn’t reproduce it showing the ' and allowing the “01-01-2019” to be converted to date… - neither with OOo 3.3.0
But using the mentioned Text to Columns function, the data is easily converted to the useful dates in any of LO, AOO, OOo…
I see that Bengali, Danish, Dutch, Faroese, Frisian, Gujarati, Hindi, Kalaallisut, Kannada, Lingala, Malayalam, Marathi, Portuguese (Portugal), Puinave, Punjabi, Spanish (Chile), Tamil, Telugu locales all have the date format like what you show; so setting your locale to these, or cell format to these, gives that '. I’m really curious what is going on there with your data/settings…
Each item is a single column, i.e.: |date|amount|tax|net| I’m stumped how to post a sample Calc file showing this. Google Cloud looks like a bit of over-kill… Since this is a PayPal input summary for tax time, I’ve had to get on with the job. It was easier to retype the 200 cells of data by hand than to try to figure out how LibreOffice processes calendar data types. Dates for taxes need not be so complicated, since the transactions only need to be sequenced in time by day. The data goes into a database. A simple number (e.g. Aug 23 = “20190823”) is good enough to keep things in order. I’ll pick it up again next year at the same time. Thx again!
Hi, not sure if I should add here or start afresh - I’m in the UK and using a mac what I’m trying to do is when I press cmd; to insert today’s date I want it to put the date in YYYY/MM/DD format. Is there a preference file for the locale setting that I can edit.
Each locale has 1 single default for each data format (e.g. data formats are “Number”, Currency, “Date”, “Time” etc. ), which cannot be changed (to be clear: The default for the specific data type and locale** cannot be changed, of course the format of a cell can be changed at any time). This default gets applied, if you enter data. In case of data is a Date and locale is “English (UK)” the default is DD/MM/YY when you enter a date. So you need to:
- either format a date cell in advance (before entering a date)
- or format after having entered a date
to get the desired result.
Note: You cannot achieve your goal using a template
Hi Opaque,
Thanks, not the answer I wanted, but saves me wasting anymore time trying to configure it.
Malc
First convert your date text to numeric dates, this FAQ may help. Sorting then already works, regardless how the display string is formatted. To display the ISO format apply the YYYY-MM-DD number format to the date cells.