Converting text to date in calc

ejoftheweb · October 13, 2022, 3:14pm

I am having difficulty converting text - imported as a csv file - to a date.

The dates are in standard European dd/mm/yyyy format in the CSV

locale en-gb

tools/options/language/date_acceptance_patterns: D/M/Y;D/M;D-M

But: data/text-to-columns/OK interprets the dates as being in mm/dd/yyyy and therefore borks on any date later than the 12th of the month.

Note to devs: AFAIK only ONE nation in the world uses mm/dd/yyyy - every other nation uses either dd/mm/yyyy or yyyy/mm/dd (with separator variations). Please don’t assume we are all American.

Version: 7.3.6.2
OS: Linux 5.15 / Xubuntu / AMD64

erAck · October 13, 2022, 4:45pm

That is not true btw:

ak_GH   '/'  (MM/DD/YYYY)
ee_GH   '/'  (MM/DD/YYYY)
en_US + '/'  (MM/DD/YYYY)
ha_Latn_GH      '/'  (MM/DD/YYYY)
hil_PH  '/'  (MM/DD/YYYY)
ilo_PH  '/'  (MM/DD/YYYY)
kmr_Latn_TR     '/'  (MM/DD/YYYY)
ti_ER   '/'  (MM/DD/YYYY)
en_BZ = en_US + '/'  (MM/DD/YYYY)
en_IL = en_US + '/'  (MM/DD/YYYY)
en_PH = en_US + '/'  (MM/DD/YYYY)
en_TT = en_US + '/'  (MM/DD/YYYY)
en_ZW = en_US + '/'  (MM/DD/YYYY)
pdc_US = en_US +        '/'  (MM/DD/YYYY)
yi_US = en_US + '/'  (MM/DD/YYYY)

ejoftheweb · October 13, 2022, 4:46pm

Thanks, that worked: BUT I think I still need to file a bug-report because “standard” should surely be locale-dependent rather than assuming we are all American…

Actually, there’s still something odd going on here: it imported them as text in the first place, so the conversion took place after import when date_acceptance_pattern or similar should have been used.

Villeroy · October 13, 2022, 4:50pm

All you need to do is checking “Detect special numbers” on the import dialog. This will interprete dates, times, currencies of all columns unless explicitly marked as text.
“Detect special numbers” is an option that should not exist. In 99% of all practival use cases you want this but it is turned off by default.

ejoftheweb · October 13, 2022, 4:51pm

thanks. Still a minority of nations, and still a barkingly illogical order.

(TBH yyyy/mm/dd as per ISO 18650 would be most sensible, as it can continue with hh:mm:ss.sssss)

erAck · October 13, 2022, 4:55pm

No, YYYY-MM-DD as per ISO 8601 is the most sensible and recognized in every locale.

( ISO 18650 is " Building construction machinery and equipment — Concrete mixers").

Villeroy · October 13, 2022, 6:39pm

If you do not check “Detect special numbers”, only digits with decimal separators and thousands separators are recognized as a number. With non-US English import locale:
2022-04-02;1,234.98
2022-05-03;9,876.12
gives 2nd of April, 3rd of May with 2 numbers 1234.98 and 9876.12. When “Detect special numbers” is unchecked, the dates are imported as literal strings unless you mark the column manually as YMD

karolus · October 13, 2022, 4:18pm

the “Date_acceptance_pattern” doesn’t matter on csv-import!

In the csv-import-Dialog you should set the right Language, and in some corner-cases you need to explicitly change the "date"columns in the Preview from Standard to Date d-m-y

erAck · October 13, 2022, 4:54pm

Either define the proper locale or date order during CSV import as karolus lined out, or if it’s too late for that then convert text to date as per this FAQ, or in the Text-to-Columns dialog set the column type to the proper date order. That dialog btw is not nailed to the English-US locale, but your LibreOffice default locale (usually inherited from the system locale if not overridden under Tools → Options → Language Settings → Languages, Locale settings).