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

English
, , ,
#1

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 )

#2

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

FAQ

#4

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 ready to help with a few hints and a bit of custom code.

#5

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 …

#6

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: anotherDateFormatIssue.ods

#7

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

#8

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

#9

@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,

#10

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.

#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!

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