Download it now!
Bug 116579 - Date format setting not recognised
Summary: Date format setting not recognised
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: All All
: low minor
Assignee: Eike Rathke
Whiteboard: target:6.1.0
Depends on:
Blocks: Number-Format
  Show dependency treegraph
Reported: 2018-03-23 07:15 UTC by Elmar
Modified: 2019-07-12 00:25 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

date format issue (39.00 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-03-23 07:23 UTC, Elmar
date format within cell styles (41.18 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-03-23 08:20 UTC, Xavier Van Wijmeersch
screenshot of 140822 (65.52 KB, image/png)
2018-03-23 16:05 UTC, Elmar

Note You need to log in before you can comment on or make changes to this bug.
Description Elmar 2018-03-23 07:15:19 UTC
ISO format for date is y-m-d.
To input d/m and d/m/y is often easier.
So I set in options to use both
However, the y-m-d format is not recognised.

Steps to Reproduce:
1. open new doc
2. type date in format d/m/y, recognizes date format 
3. type date in format d/m, recognizes date format
4. type date in format y-m-d, dose not recognize date format

Actual Results:  
see example

Expected Results:
should convert 18-3-23 to date format

Reproducible: Always

User Profile Reset: Yes

Additional Info:
[Information automatically included from LibreOffice]
Locale: en-US
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Linux (All)
OS is 64bit: yes

Build ID: 2dadf90aa7bb03d895abc05ec93ca116eb9bacbd
CPU threads: 4; OS: Linux 4.13; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-03-20_23:08:53
Locale: en-ZA (en_ZA.UTF-8); Calc: group

Tested in v5.4 - results are the same (so this is not a reversion)

User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:59.0) Gecko/20100101 Firefox/59.0
Comment 1 Elmar 2018-03-23 07:23:39 UTC
Created attachment 140821 [details]
date format issue
Comment 2 Xavier Van Wijmeersch 2018-03-23 08:20:39 UTC
Created attachment 140822 [details]
date format within cell styles

I cannot reproduce the problem, did some test with direct formatting and with cell styles. So i suggest to use cell styles if possible. Inserting date like this 23-3-2018 or 23/3/2018; i did have the correct value of 18-3-23 as your local settings.

Comment 3 m.a.riosv 2018-03-23 12:51:07 UTC
If I'm not wrong the ISO is with the whole year 2018 not 18, 2018-3-23 also works fine for me.

The issue is that 18-3-23 it's an ambiguous date, the year can be 18 or 23, in fact introducing a date like 35-3-23 gives the correct date 2035-03-23.

IMO not a bug.
Comment 4 Elmar 2018-03-23 16:03:58 UTC
I beg to differ.
Whilst you are right that ISO standard is full year, that is not the point.

The point is that I define in the Options that Y-M-D is a valid input format. and that is recognized as text, not as a date when I do type it 18-3-13, whilst I can type 13/3/18 and it does convert it to date. as the example shows.

why wound 18-3-13 be ambiguous if 13/3/18 is not?

Further, I downloaded Xavier's file and got the same result as before. How come? Please explain and then I will go away.
Comment 5 Elmar 2018-03-23 16:05:14 UTC
Created attachment 140830 [details]
screenshot of 140822
Comment 6 Elmar 2018-03-24 03:18:27 UTC
And, to add to this, whilst I do not want to draw negative comparisons to Excel (given its massive financial and talent resources countered by its sometimes predatory practices), in Excel if I type <13mar> it recognizes this as 13 March 2018 from as far back as I can remember.

As to standards: standards are not the law of the Medes and Persians which must be adhered to at pain of death.

I think of a useful extension to Thunderbird where the the author insisted that the standard says the signature must be at the bottom, after the text. Maybe. But who in practice will scroll down a long exchange to go and look for this when everyone else violates that principle? 

The pragmatic approach is to make the "right way" the default, and allow the use to change this to make it easier for them to do (after 40 years of typing on computer keyboards, I still only use two or three fingers, and I guess the majority of users are like me.) Is that not what the point of the options is?

We look to these tools to make life easier for us as our workload increases, and that it will take care of the standards (the most persistent one in this case is that the date is stored as the integer portion and the time as the decimal portion - that is the standard that counts. Not trying to force everyone to type it in in a universal way.

Finally, even standards include the principle of tailoring - adjusting the standard to suit culture. At least there always a reference back to the "root standard".

I say this as humble participant in the writing of standards (ISO and others) for many years.
Comment 7 Buovjaga 2018-03-29 14:14:46 UTC
Eike: what do you think?
Comment 8 Xavier Van Wijmeersch 2018-03-29 15:18:24 UTC
test it again with =today() same result 18-3-29
Elmar try just 29/3 or =today() it give me 18-3-29
first you need to create cell styles and then introduce 29/3 and not like a say before 29-3-2018, my mistake.
And if i remember correctly Eike did comment this issue elsewhere, but i don't remember wish bug report.
Comment 9 Gerhard Weydt 2018-04-26 23:46:28 UTC
Hi Elmar,

I've spent a lot of time to figure out what you really want to achieve, and it becomes ever more nebulous.

It starts with your statement "should convert 18-3-23 to date format". This already poses some questions:
You state in your example files that y-m-d is the standard format for South Africa, so why would you expect a conversion?
What do you mean by "... to date format"? Do you wish to have another date format? Which?
Wikipeda says ( that South Africa has accepted the ISO standard, which means that dates can/should be written as YYYY-MM-DD, but that  d/m/y is still commonly used (so how can you say that y-m-d is the standard format?); and it seems that LibreOffice still uses this "old" format as its default for South Africa, according to my tests. It's the same in Germany: ISO is the standard since at least 18 years (I delved into the matter when I managed the year 2000 project in my company), but still nowadays people rarely use the ISO representation. So I think the default date format d/m/y used in LibO is the best choice as user friendliness is concerned, although I would wish for using the ISO date for principal reasons.

Date handling is a very complex problem, and I can't say that I understand all its ramifications. But I think that in LibO these rules apply:
1. there is a standard date representation used, for which I do not know where to find the definition. For South Africa it seems to be y/m/d which does not fit  the wikipedia statement: if I add y.m.d. as date acceptance pattern, a given date 1.2.3 will be converted to 1/2/3.
2. The date acceptance patterns define which string representations will be interpreted as dates and consequently be converted to the standard date representation for this locale. My test y.m.d, using a german style for a date, is an example.
3. Entries similar to ISO dates are treated as those: 18-3-4 is converted to a correct ISO format 2018-03-04, and they are not converted to the standard format used in 1.

I think this is the best one could do, viewing that ISO is a standard accepted in these countries (I never looked into the situation of a country that had not accepted ISO), but common usage is different: present dates in the format commonly used, but retain a (standardised) format for entries in ISO-conforming format, for then it is assumed that the user intentionally uses this format.

I hope this makes matters clearer for you. I have the suspicion that you wish LibO to perform some conversion of dates which you do not exactly know how to specify, but I cannot guess which.

The comments in your documents:
Col B is as as input
Col C shows how I input the info (without the <>)
are a problem for me (and probably for many people), because you use "input" in both cases, whereas I assume that one column (B) is the result of the input after the standard conversion by Calc.

Consider these remarks, and if you still think there's something wrong with LibreOffice, provide precise informatiuon.
Comment 10 Eike Rathke 2018-04-27 11:06:37 UTC
Much writing without focusing on the real problem and the twisted setup.
So to recap:
* the system locale is en-US
* the LibreOffice locale is set to en-ZA
  which has a default date format of YY/MM/DD
* manual date acceptance patterns: Y-M-D;D/M;D/M/Y

I tried that and the input of 18-3-23 perfectly yields the date 18/03/23

However, in your sample document all cells are hard formatted to the number format locale en-GB (English (UK), which internally has date acceptance patterns D/M/Y;D/M;D-M that might be related), tried also that and then indeed 18-3-23 is not accepted as date.

Are you actively trying to confuse Calc's date input?
Comment 11 Eike Rathke 2018-04-27 11:09:37 UTC
(In reply to Eike Rathke from comment #10)
> * the system locale is en-US
Or actually it's en-ZA (en_ZA) as well, I was confused by the "Locale: en-US" which may be the browser's locale, but the important part is
Locale: en-ZA (en_ZA.UTF-8)
Comment 12 Elmar 2018-04-28 10:33:36 UTC
OK. Let us step back.
There are three issues that should not be confused:
1. how data that is stored.
2. how the date is presented (displayed).
3. how the date can be input

1. this is a matter that (should be) transparent to users, and they should not have to care about that. (And of course we wish MS/IBM hadn't created the 1904 issue.)

2. how it is displayed depends on who the audience is.

3. this is what my issue is about. 

We type dates often (having to type 2018-12-04 is a lot more keystrokes than 4/12. Especially when one uses two fingers to type.

Of course 4/12 looks like arithmetic, and so does 4-12, but I do not start the input with = or - or + so that is OK. Calc does not interpret my input to be a formula.

(I have just discovered that if I type /412/ or /4/12 Calc displays the digits between the // as italic, but the number 412 is a normal integer.)

For anyone who has to use Excel a lot, it is a pity that Calc does not automatically interpret "4dec" intuitively to mean "fourth of December 2018", but I can live with that. I like that feature in Excel because it is more likely to be correctly interpreted. I do appreciate that in German this would have to be 4dez, Spanish 4dic, and that would probably add a whole lot of code turning Calc into bloatware, that would not be good.

As to the standards and multiple regional formats. I have never understood the US method 0f m/d/y. 

(Some?) European format 4.12.18 looks like the Dewey system and thus could be confusing. And of course if I input 4.12 then Calc can rightly not know if this is a date or a rational number I am inputting.

ISO says yyyy-mm-dd and that makes the most sense when sorting an alphanumeric field. But I don't believe there is any human who says today is "two thousand and eighteen the twelfth month the fourth day", unless maybe they are autistic. So you say it differently from the way you write it which requires more mental effort.

Any country is filled with people who come from all over. Attempting to get them to follow standards is like herding cats, or pushing a long piece of rope; i.e. impossible.

Some are comfortable with UK formats, some with US, some with EU, etc...

I am living in SA (South Africa). Currencies thus needs to show Rand (ZAR or R). Thus my region is set that way. The SA standard is comma in place of decimal point, but no one (except the proverbial autistic) does that. We almost all write R1,000,000.00; NOT R 1 000 000,00. (In any event these are display formats - I would have input this number 1000000, or 1,000,000 and Calc correctly removes the commas when storing the input.

My fingers just seem to find the number and slash keys more easily. Not sure why.

However, here is the real issue:

If you, as the honourable and generous developers, give me a setting that allows me to use any one of the formats that I have identified in 
<Tools/Options/Language Settings/Date acceptance patterns> 
then I expect Calc to interpret this as a date and store it correctly.

So, if the options say "Y-M-D;D/M;D/M/Y" then in means that if I type 18-12-4 or 4/12 or 4/12/18 they should all be interpreted as 4 December 2018.

Note that I have carefully not allowed for D.M or D.M.Y; because 4.12 could be interpreted as the rational number and 4.12.18 as text. 

BUT, if 
<Tools/Options/Language Settings/Date acceptance patterns> 
said "Y-M-D;D/M;D/M/Y;D.M;D.M.Y"
and if I had formatted the cell as a date, then 4.12 or 4.12.18 should also be interpreted as 4 December 2018. 

i.e. the current year and century is always assumed unless I specify it differently, so when I input 4.12.17 or 4/12/17 or 17-12-4 are interpreted as 2017.

I may be setting up my display options wrong, but that should have no effect on Calc if I have used the
<Tools/Options/Language Settings/Date acceptance patterns> 
feature when I input data.

As a list note, the reason why I have set the display options that way is because that seems to give me the best results a number of years ago.

Does this make more sense?
Comment 13 Eike Rathke 2018-05-02 16:59:21 UTC
And at the same time the user wants to be able to edit the date as displayed by the current format's date edit format..
Comment 14 Commit Notification 2018-05-02 20:17:45 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

Resolves: tdf#116579 consider both work locale and format locale date patterns

It will be available in 6.1.0.

The patch should be included in the daily builds available at in the next 24-48 hours. More
information about daily builds can be found at:

Affected users are encouraged to test the fix and report feedback.
Comment 15 Commit Notification 2018-05-03 07:09:54 UTC
Stephan Bergmann committed a patch related to this issue.
It has been pushed to "master":

Revert "Resolves: tdf#116579 consider both work locale and format locale date patterns"

It will be available in 6.1.0.

The patch should be included in the daily builds available at in the next 24-48 hours. More
information about daily builds can be found at:

Affected users are encouraged to test the fix and report feedback.
Comment 16 Commit Notification 2018-05-03 22:29:23 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

Related: tdf#116579 tell SvNumberFormatter the proper NfEvalDateFormat

It will be available in 6.1.0.

The patch should be included in the daily builds available at in the next 24-48 hours. More
information about daily builds can be found at:

Affected users are encouraged to test the fix and report feedback.
Comment 17 Commit Notification 2018-05-03 22:30:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

Revert "Revert "Resolves: tdf#116579 consider both work locale and format...""

It will be available in 6.1.0.

The patch should be included in the daily builds available at in the next 24-48 hours. More
information about daily builds can be found at:

Affected users are encouraged to test the fix and report feedback.