Description: 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 Version: 6.1.0.0.alpha0+ 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
Created attachment 140821 [details] date format issue
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. Regards
If I'm not wrong the ISO is with the whole year 2018 not 18, 2018-3-23 also works fine for me. https://en.wikipedia.org/wiki/ISO_8601#Dates 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.
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.
Created attachment 140830 [details] screenshot of 140822
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.
Eike: what do you think?
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.
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 (https://en.wikipedia.org/wiki/Date_and_time_notation_in_South_Africa) 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.
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?
(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)
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?
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..
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=dfb9138b8b5a239b46f189a717999bcaff19aa79 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 http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Stephan Bergmann committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f5a56c367fba1c42b4f9719b10ff3e86ad5e2ab1 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 http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=657668c378a9405c9978c553be52346b250e8d99 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 http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a5e0fda3c1375f312ba8d3f0f5ce80d16649c4f5 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 http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.