Description: When Calc encounters strings look like Year-Month-Day, the interpretation varies unexpectedly. If the year value falls between 1 and 12, Calc interprets the string as Text. However, Calc correctly interprets the string as a Date when the year value is either 0 or any number between 13 and the maximum supported year. However, when a cell is pre-formatted as YYYY-MM-DD, year values between 1 and 12 are correctly interpreted as dates. The issue appears more complex than initially reported in https://bugs.documentfoundation.org/show_bug.cgi?id=164227, raising two important questions: 1. The examples demonstrate that the preset cell format affects how entered strings are interpreted into Calc's internal types and values. Is this behavior intentional or is it a bug? 2. When a cell's format is set to "General" and a string is entered, does Calc: - interpret it deterministically (always the same way), or - interpret it conditionally based on the environment (e.g., the types of surrounding cells)? Steps to Reproduce: 1. Enter "12-1-1" in one cell 2. Enter "13-1-1" in another cell 3. Set the format as YYYY-MM-DD first, enter "12-1-1" Actual Results: 1. Text 2. Date 3. Date Expected Results: 1. Date 2. Date 3. Date Reproducible: Always User Profile Reset: No Additional Info: Version: 24.8.3.2 (AARCH64) / LibreOffice Community Build ID: 48a6bac9e7e268aeb4c3483fcf825c94556d9f92 CPU threads: 8; OS: macOS 15.0; UI render: Skia/Metal; VCL: osx Locale: en-US (en_CN.UTF-8); UI: en-US Calc: threaded
A missing step is: Select all the cells first, right-click - Format Cells - Language: English (USA). I confirm, but let's ask developers later. Version: 25.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: c91edf5e57a7441fbac399c7a44f26a57878e5be CPU threads: 8; OS: Linux 6.12; UI render: default; VCL: kf6 (cairo+wayland) Locale: fi-FI (fi_FI.UTF-8); UI: en-US Calc: CL threaded
What is in Menu>Tools>Options/Languages and Locales>Date acceptance pattern?
(In reply to m_a_riosv from comment #2) > What is in Menu>Tools>Options/Languages and Locales>Date acceptance pattern? The Date acceptance pattern is set to the default value: 'M/D/Y;M/D'.I have not made any changes.
(In reply to gplhust955 from comment #3) > (In reply to m_a_riosv from comment #2) > > What is in Menu>Tools>Options/Languages and Locales>Date acceptance pattern? > > The Date acceptance pattern is set to the default value: 'M/D/Y;M/D'.I have > not made any changes. Good point. Adding the pattern Y-M-D makes step 1 lead to a date. So let's close this report?
(In reply to Buovjaga from comment #4) > (In reply to gplhust955 from comment #3) > > (In reply to m_a_riosv from comment #2) > > > What is in Menu>Tools>Options/Languages and Locales>Date acceptance pattern? > > > > The Date acceptance pattern is set to the default value: 'M/D/Y;M/D'.I have > > not made any changes. > > Good point. Adding the pattern Y-M-D makes step 1 lead to a date. So let's > close this report? Adding the pattern Y-M-D seems to work. However, it is only a workaround. I wonder if we should also focus on identifying the root cause of the issue and addressing the two additional questions raised.
(In reply to gplhust955 from comment #5) > (In reply to Buovjaga from comment #4) > > (In reply to gplhust955 from comment #3) > > > (In reply to m_a_riosv from comment #2) > > > > What is in Menu>Tools>Options/Languages and Locales>Date acceptance pattern? > > > > > > The Date acceptance pattern is set to the default value: 'M/D/Y;M/D'.I have > > > not made any changes. > > > > Good point. Adding the pattern Y-M-D makes step 1 lead to a date. So let's > > close this report? > > > Adding the pattern Y-M-D seems to work. However, it is only a workaround. I > wonder if we should also focus on identifying the root cause of the issue > and addressing the two additional questions raised. Eike: what do you think? I see there is also a related bug 164227.
Bug 164227 is unrelated, except that both are about date acceptance. 12-1-1 in a locale with MDY order like en-US is treated as slightly ambiguous if the date acceptance patterns were not matched, as the 12 could be a month number. Hence the result is text instead of date. In a locale with DMY order it could be day numbers 1..31. However, we maybe could tighten that condition and say that if the input does not match any date acceptance pattern then we can force an ISO date. > 1. The examples demonstrate that the preset cell format affects how entered strings are interpreted into Calc's internal types and values. Is this behavior intentional or is it a bug? Not a bug, it's intentional as users want to be able to input/overwrite a date in the same format it is displayed. > 2. When a cell's format is set to "General" and a string is entered, does Calc: > - interpret it deterministically (always the same way), or > - interpret it conditionally based on the environment (e.g., the types of surrounding cells)? Surrounding cells don't matter.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/47b4b1633a08dd4c97d66feabe8cd3290074dc0f Resolves: tdf#164239 Can force Y-M-D to ISO 8601 if no date acceptance pattern It will be available in 25.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/c/core/+/178174 for 24-8
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-25-2": https://git.libreoffice.org/core/commit/4fabbc6b6d461472fa2f30a46c2a79f4d8a24b73 Resolves: tdf#164239 Can force Y-M-D to ISO 8601 if no date acceptance pattern It will be available in 25.2.0.0.beta2. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://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 "libreoffice-24-8": https://git.libreoffice.org/core/commit/224c0bb32232a29d08611365d25be1c340032bc8 Resolves: tdf#164239 Can force Y-M-D to ISO 8601 if no date acceptance pattern It will be available in 24.8.5. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://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 "libreoffice-24-8-4": https://git.libreoffice.org/core/commit/dd57ef3aee60b24fecbb990a3fc2fc51ce30c9e4 Resolves: tdf#164239 Can force Y-M-D to ISO 8601 if no date acceptance pattern It will be available in 24.8.4. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.