Skip to content

Editable Date Acceptance Patterns in LibreOffice

The introduction of more restrictive date acceptance patterns in LibreOffice 3.6 (see earlier blog entries here and here) generated quite some discussion whether the change was good or bad. The fact that not all locales had patterns for incomplete (only day and month) date input added to their data also added some angry voices.

Independent from that there was one thing overlooked: users want to be able to input dates using the numeric keypad and in locales with a '.' dot date separator that was not possible anymore because usually then there is no dot on the keypad due to the decimal separator being different. That certainly needed to be addressed.

There is no way to satisfy everyone with a default set of patterns, I therefore implemented a Date acceptance patterns edit field in the Tools→Options→LanguageSettings→Languages dialogue that follows the selected locale and enables users to add, edit and remove patterns.

Date acceptance patterns edit field.
Date acceptance patterns edit field.

The change is currently in master and pending review as a late feature for inclusion to the 3.6.2 release.

Example for the German de-DE locale:

  • default patterns: D.M.Y;D.M.
  • to enable additional input on numeric keypad: D.M.Y;D.M.;D-M-Y;D-M
    • if 3-4 shall not result in a date, D-M- could be used instead of D-M
    • note that to enter an ISO 8601 Y-M-D date with a D-M-Y pattern active one needs to enter a year >31 or with at least 3 digits, e.g. 011
  • instead of D-M-Y;D-M also D/M/Y;D/M could be used

Changes to the patterns become effective immediately after having confirmed and closed the dialog.

LibreOffice date acceptance patterns

Update 2012-08-31T23:08+0200 : Editable Date Acceptance Patterns in LibreOffice

Abstract: Calc's (and in Writer table) cell input now needs to match locale dependent date acceptance patterns before it is recognized as a valid date.

Previously the number formatter's input scanner was very lax in what it accepted as a "valid" date. All combinations of 2-3 numbers separated by '.' '/' '-' or the locale's date separator even with blanks in between that somehow could be interpreted as a date was accepted as such, which was especially confusing with incomplete dates containing only 2 numbers that in many cases were meant as textual input instead. For example

  • In en-US locale, M/D is a valid date input to be interpreted as day of month of current year. However, M/D/ and M.D. were accepted as well.
  • In de-DE locale, D.M. is a valid date input to be interpreted as day of month of current year. However, D.M and D/M and D/M/ were accepted as well.

In case of an input like 1.2 in a de-DE locale or others using '.' separator, meant as some sort of textual numbering, this was extremely annoying, it was interpreted as 1st of February of current year and the user had to prepend a single quote / apostrophe to suppress date recognition. Similar for 1.2.3 in locales that do not use the '.' date separator.

Now, during build time for each locale one full date acceptance pattern is generated from the existing locale data's number format FormatElement with formatindex="21" that is also used to edit dates, taking the DMY order and the defined DateSeparator. For example, in the en-US locale this generates M/D/Y from the MM/DD/YYYY FormatCode, and in the de-DE locale D.M.Y from the DD.MM.YYYY code. For this to work correctly the separator used in the FormatCode must match the DateSeparator element defined in Separators. As for all rules there's one exception though ;) if the format code uses a different separator and that is one of the known '-' '.' '/' separators, a second pattern is generated using the format's separator. This as a generalized case for locales that for example may use an ISO 8601 edit format, as hu-HU does, regardless what the date separator is defined to.

Additionally to the date acceptance pattern every locale of course still accepts input in an ISO 8601 Y-M-D pattern, and since LibreOffice 3.5 that also leads to the YYYY-MM-DD format being applied.

Localizers, HEADS UP please

If in your locale incomplete dates should be accepted or additional patterns that vary from the generated full date pattern are needed, those are to be defined in the locale data LC_FORMAT element for which a new DateAcceptancePattern element exists, of which zero or more can occur before the FormatElement elements. Currently only the following patterns are defined as they are the only ones I knew were plausible:

  • bg-BG, a trailing breaking or non-breaking space followed by lower case or upper case Cyrillic letter GHE and a dot, as defined in the edit format
    • D.M.Y г.
    • D.M.Y г.
    • D.M.Y Г.
    • D.M.Y Г.
  • de-DE, incomplete date
    • D.M.
  • en-US, incomplete date
    • M/D
  • sl-SI, date separator dot plus space
    • D. M. Y

For example see i18npool/source/localedata/data/en_US.xml

Happy date accepting :-)

Update: an updated list of locales and patterns is available in a newer blog post.