I used to be able to enter a date in the format MM/DD and it would be recognized as a date, and as such formatted to whatever the date format set for that cell had been set to (for example, YYYY-MM-DD). (eg) I would enter 10/1, hit enter, and the cell (having a DATE format) would change it to 2013-10-1 Now, however, it just leaves it as text instead of converting it to a date. Very frustrating. Language is "Default-English (Canada)" Last worked in v3.5.7 (see http://ask.libreoffice.org/en/question/25202/how-to-get-calc-to-recognize-input-as-date-not-text-and-format-accordingly/)
I can confirm that this is indeed a bug and a regression. Changed Platform and OS to All since it was reported on Linux x64 and I confirmed it under Windows x86 (I know this wasn't tested under Mac but there is no multiple choice...)
Date acceptance patterns changed sincr LibO 3.6 in a more restrictive way. But now you have editable settings, see http://erack.org/blog/archives/22-Editable-Date-Acceptance-Patterns-in-LibreOffice.html If this answer suit you, may be the bug can be closed?
(In reply to comment #2) > Date acceptance patterns changed sincr LibO 3.6 in a more restrictive way. > But now you have editable settings, see > http://erack.org/blog/archives/22-Editable-Date-Acceptance-Patterns-in- > LibreOffice.html > > If this answer suit you, may be the bug can be closed? I see, that works. Though the setting's placement is not at all obvious (I would have looked under AutoCorrect options or Format Cells->Numbers), and it's impossible to find via web search engine and also doesn't come up when searching "date input formats" in ask.LibreOffice.org - "date acceptance patterns" is not a search term that would have sprung easily to mind, I'd suggest adding the keywords "input format" even if not strictly accurate in LibreOffice nomenclature...
REOP as per https://bugs.freedesktop.org/show_bug.cgi?id=69211#c6 . Basically, Calc is not recognizing date-formatted cells even when additional "Date acceptance patterns" are added. Calc is accepting those additional date patterns only when the date is originally inserted with the default date pattern and *then* converted to the additional date format.
Since the "Date acceptance patterns" are not working as expected, I'm CC'ing erack too.
The en-CA locale simply lacks a predefined date acceptance pattern for abbreviated date input. You can add
... hmpf.. accidentally clicked Save ... You can add date acceptance patterns under Tools -> Options -> LanguageSettings -> Languages
Btw, what do you expect when entering 10/1 in a Canadian locale, 10th of January or 1st of October? And why not 10-1 as the French and English Canadian locales both use the Y-M-D format and then it would be 1st of October?
Unfortunately Canada's official standard and it's da facto standards are pretty mixed up. Most of the time Canada winds up following US date formats simply because it's easier to go along with as it makes up so much of the cultural context. So most people would expect to 10/1 to produce Oct 1
It is not just about a specific language setting not having built-in defaults in Calc. My tests are showing that the additional "Date acceptance patterns" are not working as expected (a.k.a BUG). The "Date acceptance patterns" is supposed to help the user introduce dates using alternative date formats (other than its default in Calc Language settings). I can insert the date in my default format and *then* change the format of the cell as I want; but then there is no point for the "Date acceptance patterns". The whole point of "Date acceptance patterns" is for the user to be able to insert _other_ date formats and Calc should identify (format) it as date "automatically". This is currently not working (for me). Version: 4.1.3.2 Build ID: 70feb7d99726f064edab4605a8ab840c50ec57a on Windows.
@Ady: Apparently you're confusing date acceptance patterns (which are for input only) with date formats (which are for display strings). The input patterns determine what input is accepted as a numeric date, the format determines how the resulting date is displayed.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=2d8cac51c6d5205a27d90bd9237c8ed1e4b8deea added date acceptance patterns to Canadian locales, fdo#71664 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.
Pending review for 4-1 as https://gerrit.libreoffice.org/6726
(In reply to comment #11) > @Ady: > Apparently you're confusing date acceptance patterns (which are for input > only) with date formats (which are for display strings). The input patterns > determine what input is accepted as a numeric date, the format determines > how the resulting date is displayed. @Eike Rathke, I don't think I am confusing: A_ Calc -> Tools -> Options -> Language Settings -> Languages -> "Date acceptance patterns" with B_ ctrl+1 -> Numbers -> Date -> select a format (or use a user-defined format for date). For "A_" I use M/D/Y;M/D;YYYYMMMDD;D/M/Y;DD/MM/YYYY . And my default in Calc is set to English (USA). With these settings, when I insert 20/11/2013 I get a 'text' formatted cell, not a 'DD/MM/YYYY' format. When I input 11/20/2013 I get a 'MM/DD/YYYY' formatted cell. According to this explanation and my experience, the problem still exists and has nothing to do with a specific default language or default county (such as CA). If I am not setting the "Date acceptance patterns" correctly, please let me know. If you can replicate what I'm describing here (LO 4.1.3.2), please REOP the bug so to really correct the issue. Thank you and Best Regards, Ady
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6809b7c573fee722de97197a6242e13b3bd94a08&h=libreoffice-4-1 added date acceptance patterns to Canadian locales, fdo#71664 It will be available in LibreOffice 4.1.4. 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.
(In reply to comment #14) > (In reply to comment #11) > I use M/D/Y;M/D;YYYYMMMDD;D/M/Y;DD/MM/YYYY . And my default in Calc > is set to English (USA). That pattern string is wrong. Note also that when entering/modifying such a string the input field displays it in red letters. YYYYMMMDD and DD/MM/YYYY will not work. Input matching patterns are not number format codes, each part of Y,M,D represents numeric input and the numeric parts have to be separated by a date separator. Repeating a Y,M,D letter is not allowed. Using both, M/D/Y and D/M/Y, does not work, there is no way to distinguish an input of 1/2/3 whether it was meant as M/D/Y or D/M/Y, your English-US locale's M/D/Y order takes precedence.
Thanks Eike Rathke, I think that adding a few more defaults should do the trick nicely for us Canucks given the relative difficulty in determining the correct place to add date acceptance patterns to match those learnt from our brethern south of border :)