How do I fix text dates in LibreOffice so they sort properly?

English
, , ,
#11

@Lupp: ‘file attached to my above-above comment?’ - did it now and had something to smile …

‘We need to find a way out of all this date-format-nonsense!’ - be careful, some devs might say ‘wishful thinking’, way one: forbid all date representations except YYYY-MM-DD, force user to change on input or opening file, way two: force a format string attached to each date value,

Whenever I test something I find a bug. - welcome to the club :wink: … you forgot: 'wait some years or decades ‘til somebody takes care of’

#12

@newbie-02: “…forbid…”, “…force…”. What nasty words!
Joking aside! What I actually would claim to be necessary is a way for the user to reject the default date format coming with the locale and to replace it. If I enter a date using the one abbreviating acceptance pattern I defined I get DD/MM/YY because I’m using the en-GB locale. If I set en-US it would be MM/DD/YY. Being a German living in Germany I might be expected to use de-DE, and to get then DD.MM.YY. All these formats are bad, partly ambiguous, and then even dangerous in some cases, But if I want to get a better format, I need to set it explicitly to specific cell ranges which then will use it for every number I enter. Are we silly? Yes! Regarding de-DE there is a “national” standard for business correspondence (DIN 5008) to use ISO 8601, and explicitly deprecating two-digit-year, but nobody cares. It seems it’s not even teached to typist apprentices,

#13

"If I enter a date using the one abbreviating acceptance pattern I defined I get DD/MM/YY because I’m using the en-GB locale. If I set en-US it would be MM/DD/YY. Being a German living in Germany I might be expected to use de-DE, and to get then DD.MM.YY. "

  • A textual .csv file will not contain the locale information automatically. You (and your partners) must create a “column” for the locale marks, and you must write a converter routine, what will convert all the textual dates to numeric values based on the textual dates and the locale marks.
  • The LO can not guess the locale of the “two digit year, garbled/mixed y-m-d” type textual dates. (You can choose one locale per column in the import filter.)
#14

@Zizi64: Sorry. There may have been a misunderstanding. My statement quoted above should only illustrate my complaint concerning the fact that I cannot change the default date format of any locale I want to use - and that the actual defaults I know all are silly/bad/dangerous/…
Correctly imported dates represented the standard way by numbers in Calc, are usable without problems, of course.
Yes, CSV-style text files don’t contain locale information. This is an additional stringent reason to never export dates to csv using a non-ISO8601 format - and in fact the mentioned problem established the necessity to define a global standard. Some deciders in the business world don’t seem to know that.
(Since locales are somehow related to “nations” we should probably expect them to be little monsters anyway.)

#15

I have to assume that this question is going nowhere, and I thank everyone who tried to help. MS Excel can handle the format issues, and LibreOffice can’t, so I’m just going to switch to Excel because I still have to ger my work done at the end of the day. Many thanks, everybody!

#16

“MS Excel can handle the format issues,”

  • I do not think if the excel can handle the garbled textual date values, like the 01/02/03: How the Excel can decide if it means 2001 february 03., or 2003 february 01., or 2003 january 02.?? I suppose it, the Excel can convert all of dates according to the locale setup of the Excel, or by a choosen option. There is not any other ways.
  • Yes, you can recognize the XX>31 value as a YEAR. After this step, you can decide about the YY>12 values: they are DAYS, and the rest part is the MONTH. But what about the smaller values??
  • Yes, we know it: nobody uses the year value in the middle position of a textual date. But it is not enough information fot the correct conversion.
#17

Yes, we know it: nobody uses the year value in the middle position of a textual date

heh… In old Russian:

Января 1200 года, 4 числа., …

#18

“Января 1200 года, 4 числа., …”

  • Oooooops! :smiley:
#19

FAQ: hard stuff …

[data - text to columns] did the trick for me, while ‘datevalue()’ which worked in ex$el threw err: messages in calc,

observe some points,

  • three of your dates are ambiguities and depend on the assumption being D/M/Y rather than M/D/Y,

(D/M/Y is somewhat unusual for US regions?)

  • the success of both ‘import’ and ‘conversion’ may depend on which ‘date acceptance patterns’ you have specified in [tools - options - language settings - languages] and in which order,

  • after the conversion you can format the numeric date values with strg-1 as any date format

  • when exchanging files with date values care to have identical settings, e.g. for the date basis in [tools - options - libreoffice calc - calculations], otherwise date corruption may occur,

#20

Some useful information here! I didn’t know about “[data - text to columns]” which looks like it converts text to numbers - very useful. I wouldn’t have guessed from the vague description “text to columns”. It makes me think that all the tools are available somewhere in LO, except it’s exceptionally difficult to find them and to suss out how they work. What would be really appreciated is a way to quickly convert dates to ISO-8601, or even to a simple number in the ISO sequence. I like how Excel can manage this, and wish that LO could do something similar. Regardless, thanks for the useful comments and answer!

#21

hello @James: acc. to the ‘standards’ of this site your comment shouldn’t be written as an answer, but as a comment to or edit of your original question, afaik you can still change that,

reg. ex$el working better i had only 2010 to try, which had similar handling as LO, and i’m curious how any version should be able to solve the ambiguity of the dates in the last three lines …

#22

Thanks - This forum is limited in how to post and respond, so I was trying to find a useful place to reply and thank those who replied. There’s still no place to thank those who replied, so I’m just making do with what’s available.

#23

Same spreadsheet, similar question how-do-i-format-a-column-of-calendar-dates-in-calc

Easier to import all the PayPal .csv files into a new spreadsheet correctly than to try and fix each date manually. I can only re-iterate that you have have to identify the existing date format, Date (DMY) in the PayPal .csv, rather than the date format that you want to apply

#24

Quoting @JamesBurke:

I have to assume that this question is going nowhere, and I thank everyone who tried to help. MS Excel can handle the format issues, and LibreOffice can’t, so I’m just going to switch to Excel because I still have to get my work done at the end of the day. Many thanks, everybody!

The question would clearly have a working answer if there were consistent data (dates) Having to regard (among others) the three examples posted with the question

(1.) 08/13/2019  14:43:13    PST Completed   USD 68.21   -2.28   65.93
(2.) 21/03/2019  11:03:24    PDT Completed   USD 83.74   -2.73   81.01
(3.) 05/04/2019  12:35:13    PDT Completed   USD 30.00   -1.41   28.59

we needed to point out that neither a human brain nor a spreadsheet software can reliably make valid data from this without having access to additional information.
Trying to reading the first column as dates we find:

  1. In he first row that cannot be a date in the (bad) format used in many countries, typically in the UK, because there is no 13th month.
  2. The second row cannot be a date in the (very bad) format usd in the USA and (ooficially) next to nowhere else, because there is no 21th month.
  3. the third row gives no clue whether a UK or a USA format should be assumed.

If Excel actually conceals these facts, and returns something without any hinting to the ambiguities, you cannot get your work reliably done by it.

This is not Excel-bashing! To the contrary I precisely showed in the example attached to my second comment on the question that also LibO V7 treated the data in an unacceptable way in one of the demonstarted cases.

You thanked “everyone”, but you didn’t do what was actually necessary when taking part in a kind of forum: You didn’t resolve the contradictions and/or ambiguities and/or typos…
You didn’t even point out which one of the thinkable formats you actually expected to be applied with your examples.

I therefore cannot accept the thanks.
(I also sometimes have something to be done at the end of the day - and at tghe end of of my days, too.)

#25

I solved this by loading the csv into Excel. The three defective dates immediately showed up and could be fixed by hand. I admit that the problem would be much easier to deal with if L-O was able to crunch the dates easily as Excel is able to do. FWIW, Google Chrome has exactly the same issues, so I’m guessing the O-O, L-O, and Chrome are all based on the same foundation.

#26

OOo (Now AOO and LibO are both based on StarOffice (which has a long history). Chrome is a WebBrowser. Their GoogleSheets is an independent.spreadsheet software for online use only.

To find wriongly formatted textual dates in a list is easily done if the correct format is clearly specified, but neither was this your question nor did you tell what format you expected your dates to be.

Once again with fresh words:
A CSV actually containing a column where MDY and DMY dates are mixed can never be interpreted reliably by what software ever, as soon as it also contains entries not allowing .the needed disambiguation.
Such dates are many (132 every year) - and three of them occured among your examples. .

#27

Right from the start I was asking how to format dates as “Y-M-D format” to remove the ambiguity, in the third sentence. FWIW, the Google Spreadsheet has the same result as O-O and L-O, regardless of it being an online service. I notice that others have been trying to solve this in forums for years. I wish now that I’d tried Excel in the beginning, it would have saved everyone so much time & energy, but Excel is not easy to find.

#28

Well, you don’t still look for help, and my intention to “teach” you and other visitors to this thread concerning an aspect I regard relevant, seems to be bound to fail as is again clarified by your mention of a target format in place of the needed disambiguation of the source format.
Therefore: Over.
As a consolation: There are sayings that even renowned scientist fed their data into Excel sheets and published the erroneous results.

#29

What a mess!

OP had been asked several times to clarify. It seems that OP gets some garbage in, and wants to sort it out manually (my guess) - and they are satisfied by something that they see in Excel, and do not see in LO. I still do not understand from the discussion, what is that, and thus cannot figure out how to help the person who cannot describe it like

I open this CSV [file] in Excel, and see this [screenshot], and then I can do this in these invalid cells; but in Calc, I see this [screenshot], and then I can’t do the same …

Other questions - e.g., this - seem to just repeat the same pattern.

#30

Generally “this” isn’t a LibreOffice issue but a user issue, and I cannot fix users nor suggest workarounds. Not to consider reasonable suggestions is the main symptom of the mentioned issue. Sorry!

However, “software” and “everyday purpose IT” did a lot to enforece “world-as-it-is-vulnerability” by preferring the worst standards in many cases. Using date formats as an example I can tell, e.g. that all(?) the felt hundreds of locales meanwhile supported have a default date format they apply automatically if a date was “recognized” - and there isn’t one of these locales using a reasonable format for the purpose. Even worse: You cannot change that locale dependent default format. (I don’t know exactly since what version at least dates entered in full ISO 8601 extended are no longer reformatted to the bad locale default. They once also were.)
To hell with outrageous localization! Who forces it upon us? To hell with them, too!

How do I set LibreOffice calendar dates to ISO 8601 format?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.