Skip to content

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.


The Pathedral and the Kazoo on : Editable Date Acceptance Patterns in LibreOffice

Show preview
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


Display comments as Linear | Threaded

Stephan van den Akker on :

Good work, Eike!

This was a major source of frustration with Calc users. Glad to see it sorted out.

Is this already fixed in the latest beta releases of LO 3.5?

Does this fix also apply when importing CSV files?

Eike on :

The change is in master only, it quite changes behavior and may need some massaging here and there, so I refrained from pushing it last minute into 3.5

Yes, CSV import also benefits from this.

khagaroth on :

Hi, it's nice to see this fixed, depending on type of document, Czech language uses both decimal comma and decimal dot and entering or importing numbers with decimal dots was a huge pain.

I sent a patch (including the partitive month names and some other small changes) for Czech language to the LO dev mailing list. I hope I understood the acceptance patterns correctly. Czech language does have two date standards. One with leading zeros, where the separator is a dot and one without leading zeros, where the separator is a dot+space (or dot+nbsp if typography is applied). And as usual, people don't follow the rules, so entering dates without leading zeros AND without the space after the dot are pretty common.

Rainer Bielefeld on :

Hi, I believe for most users the new behavior is a great progress, but for me it's mess, I often have to rework lists with dates, and now I need more or less twice as many keystrokes as before. Is there any trick to get the old behavior back? Auto Replace - Replace list would be really uphill (although worthwhile for me).

Eike on :

Currently there is no option to change behavior. Maybe we should offer a possibility to edit the patterns.

Milan Niznansky on :

This is adding an insult to injury.

What if your data input (e.g. manual Copy&Paste or CSV) uses a different format then the default locale ?

Well guess what:
1) to be bale to process the input you MUST change not non-native locale

2) ALL date calculations are BROKEN on most other locales thank "compatible" configured

2) this is a SYSTEM parameter meaning a recipient had 99% probability to be staring at sheet full off #VALUE cells ...

In short, it is critical to either (preferably both)
A) be able to change the DATE format INDEPENDENT of a chosen locale (windows 95 had this feature ...)

B) Include the DATE recognition pattern as a PARAMETER OF THE DOCUMENT - as is language retained

It reflects on LO badly to have a situation where the only way to share a SIMPLE spreadsheet in EN-EN environment (UK-IR-US for instance) you have to use PDF ... :(

BTW, I wasted one hour of my time figuring our why the Lady cannot use my spreadsheet (Thanks to this idiotic "feature", LO is piece of **** by her admission at this moment and NOTHING will change that perception => I will have to move to Office 2003 for our interaction ...).

Sorry for the rant. Hopefuly this can get fixed at least to the Office 95 feature level before 4.0 ...


Cheers! and keep up the good work!

erAck on :

I don't know why I bother answering this rant ... this "idiotic "feature"" as you put it isn't available yet in a released version, so I guess your rant isn't even about the feature described in this blog post. You and your Lady could get acquainted with the features available instead, such as applying another locale's format to cells before pasting data if that data originated in a different locale, or selecting the locale in the CSV import dialog for such files. Well, good luck with Office 2003 then.

Andreas Säger on :

Can not enter dates on the num-pad anymore (de-DE).
If it aint broken don't fix it.
Adjusting to the dumbest fraction of assumed users does not pay off.
Upgrade to AOO 3.4.1

erAck on :

Insulting anyone does not help.

Better take a look at

ben on :

there is a requirement to accept both M-D and Y-M patterns, how to do? eg. 14-9 will be accept as Sep.14, which the same as 9-14

thanks in advance

erAck on :

You can add the patterns M-D;Y-M but you'll have to enter a 4-digit year to distinguish, e.g. 2014-9

But it might be that you had a typo in your sample patterns and you didn't mean to have M-D and Y-M but M-D and D-M instead, according to your example date. You can have M-D;D-M patterns but it does not make always sense because it is ambiguous for input like 9-11, the resulting date is arbitrary and depends on whatever pattern was matched first.

ben on :

Thank you for your reply. Sorry, i made a typo mistake. You corrected me. But i still found it didn't work after i added patterns M-D;D-M;Y-M in order.I test to input "14-9" "9-14" "2014-9" and only the second one be recognized a date. I use Libreoffice

When taking the input "14-9", a pattern will be find for it, and unfortunately a result pattern "M-D" will be find because it is at the first place.

So i'm afraid it can't distinguish M-D;D-M;Y-M. That is to say patterns have the same length number and the same separator will always match the first pattern.

Could you check this? Thanks for your time.

erAck on :

It seems not to work in versions up to 4.2.5, but works in LibreOffice 4.2.6 and 4.2.7

ben on :

I checked in LibreOffice and it works perfectly well. Thanks very much for your great work!

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.

You can use [geshi lang=lang_name [,ln={y|n}]][/geshi] tags to embed source code snippets.
Form options