Ask Your Question
1

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

asked 2020-08-09 06:42:54 +0100

James Burke gravatar image

updated 2020-08-09 12:57:09 +0100

Lupp gravatar image

My LibreOffice sheet has a column of dates that is confusing to read because the date formats are not consistent. How do I format a column of calendar dates in a workable way that makes sense? My choice would be Y-M-D format so that the dates are totally unambiguous. The formatting tools in LibreOffice don't work on a column of dates. Here's an example:

Date    Time    TimeZone            Status                 Gross     Fee            Net
15/02/2019  12:09:03    PST Completed   USD 30.00   -1.41   28.59
18/02/2019  12:26:11    PST Completed   USD 38.75   -1.42   37.33
22/02/2019  06:20:12    PST Completed   USD 39.90   -1.78   38.12
23/02/2019  15:04:28    PST Completed   USD 30.00   -1.41   28.59
08/13/2019  14:43:13    PST Completed   USD 68.21   -2.28   65.93
08/15/2019  17:29:33    PST Completed   USD 30.00   -1.41   28.59
16/03/2019  10:00:26    PDT Completed   USD 30.00   -1.41   28.59
21/03/2019  11:03:24    PDT Completed   USD 83.74   -2.73   81.01
22/03/2019  12:33:36    PDT Completed   USD 30.00   -1.41   28.59
29/03/2019  14:09:58    PDT Completed   USD 30.00   -1.41   28.59
31/03/2019  07:40:09    PDT Completed   USD 38.75   -1.42   37.33
03/14/2019  06:10:41    PDT Completed   USD 27.90   -1.33   26.57
05/04/2019  12:35:13    PDT Completed   USD 30.00   -1.41   28.59
08/04/2019  16:06:30    PDT Completed   USD 16.75   -0.79   15.96
11/04/2019  11:03:47    PDT Completed   USD 129.60  -5.10   124.50

(Slightly edited for readability by @Lupp )

edit retag flag offensive close merge delete

Comments

Please upload or share a real, ODF type sample file here (if it is possible in this forum). My tip: - Use real (numeric type) dates. It is a hard job to convert/re-enter the inconsistent textual dates.

Zizi64 gravatar imageZizi64 ( 2020-08-09 06:56:46 +0100 )edit
1
Mike Kaganski gravatar imageMike Kaganski ( 2020-08-09 10:22:02 +0100 )edit
1

The example is very problematic. It contains textual dates in different (bad) formats as already newbie-02 pointed out. In fact you cannot reliably import the given table.
As always in such cases, I would like to know how the data were entered/imported/generated in the sheet.
Very often the background is an import from a csv-like file or a similar PasteSpecial action. In these cases the relevant steps should be done during import with Detect special numbers enabled, and Column type: chosen properly.
Anyway; +10 for the OQ having emphasized that he wants "totally unambiguous dates", imo meaning ISO8601 extended.
Contrary to many contributors here and elsewhere, I would even suggest to consider if working with textual dates in this format would be preferrable. It might reduce the impact of silly "default" date formats set by misconceived localization.
If somebody wants to do it the mentioned way, I'm ...(more)

Lupp gravatar imageLupp ( 2020-08-09 13:17:40 +0100 )edit

hello @Lupp: much better reading - after edit of question - but you already 'did the job' as you'd convert the 'dotted dates' to 'slashed' ones, now it's hard to spot where the original problem had been ...

newbie-02 gravatar imagenewbie-02 ( 2020-08-09 14:26:10 +0100 )edit

I did not change anything in the content of the table. I only selected it as a whole and applied 'Preformatted Code' to it.
See also: C:\fakepath\anotherDateFormatIssue.ods

Lupp gravatar imageLupp ( 2020-08-09 14:43:15 +0100 )edit

@Lupp: strange ... i copied the text in total, pasted it as space separated text and ordered it to rows manually. that way i got ~5 dates in 'dotted' format, and the rest 'slashed'. think it was the last three and 5. and 6. data row 'dotted'. cannot recheck as i didn't save a copy, but as i have a vague remembering that i'd check text vs. imported data for the date column, and as the OP spoke of 'date formats are not consistent' and now they are quite consistent, i'm nearly sure that that my memory is correct.

newbie-02 gravatar imagenewbie-02 ( 2020-08-10 00:10:20 +0100 )edit

@newnie-02: I cannot tell anything for sure except my not editing the data. I only appliked the tool preformatted text.
Probably can the OriginalQuestioner solve the puzzle.

Lupp gravatar imageLupp ( 2020-08-10 00:31:20 +0100 )edit

@Lupp: you're out of suspicion, i have to correct myself, the difference is still in the data, they are not consistent as some are DD/MM/YYYY, some MM/DD/YYYY, and some the one or the other,

newbie-02 gravatar imagenewbie-02 ( 2020-08-10 07:41:15 +0100 )edit

Anyway: My LibO V7.0.0.2RC treated the inconsistency inconsistently by the import routine when I tested. Did you check the example file attached to my above-above comment?
A "felt" experience: Whenever I test something I find a bug. Many of them are so convoluted that I feel discouraged concerning the task to research them, to describe them to reproducibility, and eventually to report them to the bug-tracker.

Lupp gravatar imageLupp ( 2020-08-10 11:05:21 +0100 )edit

@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 ;-) ... you forgot: 'wait some years or decades 'til somebody takes care of'

newbie-02 gravatar imagenewbie-02 ( 2020-08-10 17:50:17 +0100 )edit

@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 ...(more)

Lupp gravatar imageLupp ( 2020-08-10 20:13:45 +0100 )edit

"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.)
Zizi64 gravatar imageZizi64 ( 2020-08-11 06:58:48 +0100 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2020-08-10 06:14:15 +0100

James Burke gravatar image

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!

edit flag offensive delete link more

Comments

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 ...

newbie-02 gravatar imagenewbie-02 ( 2020-08-10 07:57:22 +0100 )edit

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.

James Burke gravatar imageJames Burke ( 2020-08-10 18:34:21 +0100 )edit

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

Earnest Al gravatar imageEarnest Al ( 2020-08-13 13:03:58 +0100 )edit
0

answered 2020-08-13 12:41:22 +0100

Lupp gravatar image

updated 2020-08-13 12:49:33 +0100

Quoting @James Burke:

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.)

edit flag offensive delete link more

Comments

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.

James Burke gravatar imageJames Burke ( 2020-08-14 02:08:42 +0100 )edit

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. .

Lupp gravatar imageLupp ( 2020-08-14 08:26:53 +0100 )edit

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.

James Burke gravatar imageJames Burke ( 2020-08-14 09:46:52 +0100 )edit

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.

Lupp gravatar imageLupp ( 2020-08-14 10:17:51 +0100 )edit
0

answered 2020-08-09 11:57:11 +0100

newbie-02 gravatar image

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,

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-08-09 06:42:54 +0100

Seen: 149 times

Last updated: Aug 13 '20