This issue comes up every year as I have to merge sales results from different regions. I’m not able to convert calendar dates in LibreOffice to any kind of useful common format, e.g. ISO 8601 (YYYY-MM-DD). A related question is how to upload a sample .ods file to show the problem (LO says it’s not permitted). I see from my history that I’ve been working on this issue for about 10 years so far, and still haven’t figured out how to do it with the tools available. I’d like to find a simple, direct way like can be done with ordinary database or Access applications.
I can’t give you a complete technical answer, but a “trick” I learned from someone else here about a year ago – whenever I import data in csv format I choose the locale of the import as English-UK and then select from the list the format 1999-12-31 (or whatever it is) to give me a uniform YYYY-MM-DD format conforming to ISO-8601. It is still sometimes necessary to change the format of the imported data in the date column in my spreadsheet (menu Format → Cells → Date) to the same YYYY-MM-DD format, but it all works out in the end. Hope this helps.
IMHO, handling of dates from other people’s data is a constant source of frustration due to the many different formats used by people. However, I doubt that they have this problem in North Korea. But then …
I understand what you’re saying, but can only convert some dates, but not all. So the date data becomes a jumble. I’m thinking this is a common task and tools should be available to do this, but no luck so far.
tools should be available to do this, but no luck so far.
There are tools to do that and almost all errors happen based on misunderstanding of options in import dialog, when importing a .csv file. This misunderstanding finally ends up in “dates” being incorrectly imported as text. The important thing in import dialog is: Tell LibreOffice how the dates are in the import file, and do not set options according how you want your data to appear after import. Example of the common misunderstanding: csv data contains date in format D/M/Y (UK) and you want 'em to appear in M/D/Y (US), then many people choose: M/D/Y in the Column type drop down for the respective column. And that’s wrong (and the cause of subsequent problems). You need to select D/M/Y, because that tells LibreOffice: The data to be interpreted on input are dates formatted as D/M/Y and only doing so enables LibreOffice to convert the string to real calc dates (=integers) correctly.
…Cont’d…
All formatting, to stick with your example, to ISO 8601 must only happen after the import is done (immediately after the import, all dates appear according to the defaults of your language [locale] settings)
Aside – Thank you, @anon73440385, your explanation of the process is excellent. None of the documentation that I read at the time of my problems made this as clear as you have. Hopefully, this will help the OP as well.
It sounds good so far, except I can’t make it work. Example: here’s how my date column looks:
2020-01-01
2020-08-01
2020-11-01
18/01/2020
25/01/2020
27/01/2020
30/01/2020
It looks like the first 3 rows are non-ISO “Y-D-M” sequence. But there is nowhere on earth that uses a “Y-D-M” calendar date format. The rest are in D-M-Y format which might be somewhere in Europe? I’m not familiar with Europe to that extent, so I’m just guessing. Regardless, when I try “Format>Cells>Date>1999-12-31” I get that unusual “Y-D-M” format for the day dates of 12 or less, all others remain unchanged. So there’s 2 L/O questions remaining: (1) How to change ALL L/O dates into a similar format of any kind? and (2) How to change L/O’s “Y-D-M” format into a standard ISO8601 “Y-M-D” format? I’m trusting that the LO “Y-D-M” format is some kind of fixable error. I hope that makes sense!
BTW, It would be useful if I could put up a LO code snippet, but LO-Ask doesn’t allow code snippets.
but LO-Ask doesn’t allow code snippets.
- edit you question if you want to upload a file or
- use pre-formatted text icon
to format text as code
I expected when writing my comment that you will show me a single content having already mixed and messed date formats. This is not what I have been talking about. For sure nobody can help, if a single .csv file already uses a mix of different date formats and more worse non-existing formats like Y-D-M. Then the problem already has happened somewhere else and you are the poor guy forced to fix others peoples failure to stick with a common date format. No easy way out of this and especially not by formatting. Formatting never fixes incorrect content. Additionally: If you show me the textual representation, I can’t tell whether you deal with real dates or text. So please upload a sample .csv. I have a gut feeling that we start to mix things here:
- Import of a csv file with dates correctly vs.
- Fixing a messed import
Ask LibreOffice doesn’t permit uploading samples, so I’m not sure how to show you. It’s a very simple issue to display, and it’s quite easy to see and understand - except I can’t show it to you here. I’ve lots of examples to show, except I don’t know how to show them.
Ask LibreOffice doesn’t permit uploading samples,
It does - and I have provided the link where you could do that. Here is the link once more: edit (your question - and the link is right of the word Comments just below your question and icon showing a pencil ) and use the paperclip symbol when you have entered the edit mode
There are only a restrictions to to file size and file extensions (in that case, just add a valid extension to the filename and leave a comment about the fact)
Sorry - I’m missing something. From where I sit, adding a LO file is not permitted. I don’t see what you are showing, e.g. the paper-clip attachment icon. AFAIK, this is prohibited. Please tell me more if you think it’s possible.
From @TAB - Answer deleted.
Edit the Date field. Click Additional formats, choose Languages: English (Canada).
If dates are represented by numbers, as it’s generally the case in spreadsheets, there only are two questions:
-1- Are they correctly created - entered - imported?
-2- Were they originally created for the same day-zero-setting as is currently used?
Both these questions are not eligible for an anylysis based on sheet contents. If doubts arise the process of creation, entering, import must be analyzed or repeated correctly.
If correctness can be assumed, the subsequent formatting is trivial, and supported by spreadsheets to the full extent.
Unfortunately many users prefer traditional, regional, and culture-dependent formats. Unfortunately again software seems (devlopers think) to be forced to support these different formats. A clever user will exclusively apply ISO 8601 extended: YYYY-MM-DD.
If dates are represented as texts, there are basically the following cases:
-a- ISO 8601 is used.
-b-The used format is explicitly stated like 03/11/21 (en-USA, 1930<=year<2030)
-c- There are very many dates scatterd over many years, months, days, and the complete sample is consistent under the assumption of one specific date-format for all of them, and inconsistent under every thinkable different format.
-d- There are dates that cannot be consistently interpreted assuming one specific format.
Case -a- is obviously OK, and should be the only one actually used, but isn’t.
Case -b- next to never occurs. In fact it is rather impractical.
Case -c- can be stated based on thorough analysis of large samples, but the resulting assumption is a “most likely guess”.
Case -d- makes the sample definitely unusable. It’s simply no data, but just litter needing do be cleared away. Judging from this other question of yours (@JamesBurke ) this seems to be the case underlying also this question. There the first example “date” (and others) can’t be US style MM/DD/YYYY, but the fifth one (and others) can’t be the alternative DD/MM/YYYY.
No human and no software can help you therefore with this problem. You need to make sure that you get reported in an unambiguous way. Force everybody to report exclusively using ISO 8601 extended. Concerning date-time-values you may replace the specified separator T by a space without risking misunderstandings.
It’s clearly unacceptable if people stubbornly insist on using a regional format, but then expect others to interpret “data” exported as texts based on these formats. Are we crazy? Yes.