LibreCalc: change default date format: feature request

braut · January 25, 2024, 10:31am

It’s absolutely not obvious that a hyphen would be included, since the default formats which sort properly are

YYYY-MM-DD (which is the most common interpretation of ISO 8601)
YYYY MM DD (which was named here)
YYYYMMDD (which is also part of ISO 8601, but less suitable for human reading and more for automated operations)

…and several “abominations” such as

YYYY_MM_DD (one of my banks does that)
YYYY/MM/DD (which is just weird and wrong to me)
YY-MM-DD (may work, may fail)
YY-M-D (even worse)
YY/MM/DD (to make sure that someone will mix not only days and months, but years also)
YY/M/D (the worst of all bad ISO misinterpretations)

…which can be even more abused by adding times:

YYYY-MM-DD HH:mm:ss or
YYYY-MM-DD HH:mm
…skipping now the date part
when HH:mm:ss can not be used on MacOS file names since the colon is a special folder delimiter and this and the other folder or path delimiters / and \ should be omitted, so
HH-mm-ss and HH-mm (and HH) should be acceptable, too.

Oh, and BTW ISO 8601 is also common in Sweden. So your wanted LANG setup might be e.g.

Language: German (+English) for your text, spellcheck, grammar
Menu Language: English (for a consistent user interface)
date format: YYYY-MM-DD (plus extended and subsets, plus tolerance for the more common local form
number format: # ##0.00 (unfortunately, in Germany we are struck with a decimal comma and a thousands separator dot) (plus local acceptance and variations)

Fortunately, Calc offers a wide variety of choices. The Setup permits you to define your preferred format(s). However, am I right that it does not accept or request YYYY-MM-DD, but Y-M-D only?

(MacOS look)

mikekaganski · January 25, 2024, 10:47am

Note that date acceptance pattern is not a formatting code, nor a mask. Its goal is to define the order and separators. Note also, that it is absolutely not needed to explicitly define an acceptance pattern for ISO dates, because these are accepted unconditionally, in all locales.

braut · January 25, 2024, 11:17am

Note that date acceptance pattern is not a formatting code, nor a mask. Its goal is to define the order and separators

Thanks for the clarification!

Villeroy · January 25, 2024, 1:23pm

Switch the locale from “German (Germany)” to “English (Canadian). Now all dates will be displayed in Y-M-D format unless they are text. If you prefer decimal comma decimals, apply a Swedish locale.
The text “1999-12-31” is not the same value as the formatted number 1999-12-31. menu:View>Highlight values” highlights numbers in blue.

braut · January 25, 2024, 1:42pm

The TEXT is not the same as a DATE. However, the strict text can be transferred to a date very easily.

The date format has many advantages, such as different kind of representations (such as “Monday, January 24. 2024”) and for calculations (such das number of days between two dates).

One of the advantages of dates is to sort with them properly. But that’s exactly that topic where ISO dates as text are as good as dates - and even may be significantly better

since you may use variations, too - such as

2024 (year only)
2024-10 (year and month)
2024-Q3 (3rd quarter)
2024-W50 (week 50)
21th century
1920 ca.

Villeroy · January 25, 2024, 3:23pm

90% of all spreadsheet functionality depends on numeric values. Apart from correct sorting, numeric dates can be filtered by year, month, day. You can generate pivot tables grouped by year, month, day. Date functions depend on true dates. Everything works reliably out of the box with numeric spreadsheet values, and no formatting will ever yield wrong results, even if you format your dates as currency.