In the following case it is impossible to EDIT a date/time value. Steps to reproduce: - Open Libre Office Calc, creating a new spreadsheet - Under "Tools" -> "Options" -> Language Settings" -> "Languages" -> "Date acceptance patterns" enter "D.M.Y" (without quotation marks) - In the cell A1 enter a value "2023-05-27 09:00" (without quotation marks) -> We see "###" in the cell - Enlarge column width to see a value -> We see "2023-05-27 09:00:00" - Change format code ("Format" -> "Cells") from "YYYY-MM-DD HH:MM:SS" to "DD.MM.YYYY HH:MM:SS" -> We see "27.05.2023 09:00:00" in the cell and "05/27/2023 09:00:00" in the "Input line" - Press "F2" to edit the value -> Value in the cell changes to "05/27/2023 09:00:00" - Edit the value (e.g. replace the last zero by the same value, zero), press "Enter" -> Formatting changes to left-justified, the value is not a date/time anymore but text. Any formula based on this cell (like "A1+1") shows "#VALUE" - The same happens if I try to edit a value in the Input line. Expected behavior: - On Edit, the value should be formatted such a way, that it will be recognized back. - It should be possible to enter "27.05.2023 09:00:00" as date/time.
Not reproducible in Version: 7.5.3.2 (X86_64) / LibreOffice Community Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3 CPU threads: 4; OS: Linux 6.1; UI render: default; VCL: kf5 (cairo+xcb) Locale: zh-TW (zh_TW.UTF-8); UI: zh-TW Calc: threaded Edit the cell and press Enter, the cell is well displayed with the format I set (DD.MM.YYYY HH-MM-SS)
I have replicated this behavior. The behavior depends on language settings and date pattern recognition. Having "D.M.Y" and introducing "2023-05-27 09:00" (which are not initially a match in format) is not the same as having a different pattern recognition with a different language setting. But I am not going to set this to "new" myself, because there are a lot of similar reports about similar problems. Moreover, the cell format is still marked as date, while it behaves as text. I'll let someone else decide to set this as "duplicate", as "new", as "not-a-bug", or whatever. From users' perspective, the behavior doesn't make sense, although someone will find some technical explanation as why this is correct and what users should do.
If anyone finds similar case, please link, I could not find it. The problem is that if I want to edit the cell, the value is presented in the format that the software is unable to read. This is IMHO a bug. Possible solution would be either - for edit, display the value using the format the software can recognize OR - after edit, recognize using the pattern used for the display. For example, one could use "YYYY-MM-DD HH:MM:SS" for edit as it is recognized always independent from the "Date acceptance patterns" setting. By the way, the Input line is already formatted this way when cell format is "YYYY-MM-DD HH:MM:SS". However, for some reasons, it is changed to "MM/DD/YYYY HH:MM:SS" if I set cell format to "DD.MM.YYYY HH:MM:SS". Why? If the user has set cell format to something different (like "DD.MM.YY HH:MM"), it would be nice to use similar format (in this case "DD.MM.YYYY HH:MM:SS") to display AND to recognize the edited input.
(In reply to Serg A from comment #3) > The problem is that if I want to edit the cell, the value is presented in > the format that the software is unable to read. This is IMHO a bug. You *forced* it to do so, by setting the only date acceptance pattern to D.M.Y but your locale obviously uses something different if ... > for some reasons, it is changed to > "MM/DD/YYYY HH:MM:SS" if I set cell format to "DD.MM.YYYY HH:MM:SS". Why? What is your locale? Likely en-US or derived (because of the M/D/Y order in the edit date format). An existing date to be edited is presented in the locale's date format unless the display format is an ISO 8601 YYYY-MM-DD format. Please copy-paste the information from the Help -> About dialog. > If the user has set cell format to something different (like "DD.MM.YY > HH:MM"), it would be nice to use similar format (in this case "DD.MM.YYYY > HH:MM:SS") to display AND to recognize the edited input. No, because you can set _arbitrary_ display formats that can't necessarily be parsed or the user wouldn't even know how to enter (e.g. other locales' month names or even calendars). (In reply to ady from comment #2) > Moreover, the cell format is still marked as date, while it behaves as text. No. The input wasn't recognized/accepted as date hence the cell content is kept as text.
(In reply to Eike Rathke from comment #4) > (In reply to ady from comment #2) > > Moreover, the cell format is still marked as date, while it behaves as text. > No. The input wasn't recognized/accepted as date hence the cell content is > kept as text. While following the procedure, the cell _is_ first recognized as date when the date is first introduced. "=A1+1" gets a date as result. Only after the "fake" edit, the behavior changes to text. After following the complete procedure as presented in comment 0, the cell shows and behaves as text, while: * the Format code shows the same "DD.MM.YYYY HH:MM:SS" as before; and * the toolbar shows the format as "date". Yes, the format is now "text". We, users, might not be doing the right thing. I'm saying that it is easy for users to be confused by the contradicting information presented versus the behavior after the ("fake") first cell edition: the cell was behaving as "date", showing format as "date", and suddenly it behaves as "text" **while still showing some other info as "date" **. Yes, there is Value Highlighting, and ISTEXT() and similar functions, and yet users get confused. I have no idea whether there is some way to improve this – this is not the first time we've seen this problem.
(In reply to ady from comment #5) > Yes, the format is now "text". No. The cell _content_ is text. And text is not affected by a number format except @ formats. The number display format is still Date, but there is no numeric content.
(In reply to Eike Rathke from comment #4) > Please copy-paste the information from the Help -> About dialog. Here it is: --- Version: 7.4.6.2 / LibreOffice Community Build ID: 5b1f5509c2decdade7fda905e3e1429a67acd63d CPU threads: 6; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded --- Yes, I use the setting "English" because I want the interface to be in the developer's language. However, I feel the date in the "day-month-year" way, not the "month-day-year" way. As a user, I want to enter the dates as "day-month-year" and to be able to edit it. I don't want to switch to German, because then simple things like "IF" will become "WENN" or even much worse. Other office suits (like Microsoft Excel) have no problems with it. Once again, the users expect from the software that if something is offered to edit, the software will understand the input correctly at least if no information was altered. Here it is not the case. When some settings in different independent places make this impossible, then the settings design may be improved.
(In reply to Serg A from comment #7) > I don't want to switch to > German, because then simple things like "IF" will become "WENN" or even much > worse. FWIW, menu Tools > Options > LibreOffice Calc > Formula > Formula Options > Use English function names.
(In reply to Serg A from comment #7) > Locale: en-US (en_US.UTF-8); UI: en-US As assumed.. > Yes, I use the setting "English" because I want the interface to be in the > developer's language. However, I feel the date in the "day-month-year" way, > not the "month-day-year" way. As a user, I want to enter the dates as > "day-month-year" and to be able to edit it. I don't want to switch to > German, because then simple things like "IF" will become "WENN" or even much > worse. Simply continue to use the en-US English (USA) UI language, but switch the locale to either de-DE German (Germany) for DD.MM.YYYY dates or to en-GB English (UK) for DD/MM/YYYY dates, instead of fiddling with date acceptance patterns in that weird way. > Other office suits (like Microsoft Excel) have no problems with it. Other suites do not use date acceptance patterns at all. You can input a string that wouldn't be a date in your locale but is converted to an unexpected date anyway. > Once again, the users expect from the software that if something is offered > to edit, the software will understand the input correctly at least if no > information was altered. Here it is not the case. Users should understand that when tweaking options derailing from presets and forcing options that don't make sense it will have consequences. > When some settings in different independent places make this impossible, > then the settings design may be improved. Maybe best to restrict users' freedom and not accept such setting that totally contradicts the locale's date pattern.
(In reply to Eike Rathke from comment #9) > > Yes, I use the setting "English" because I want the interface to be in the > > developer's language. However, I feel the date in the "day-month-year" way, > > not the "month-day-year" way. (...) > Simply continue to use the en-US English (USA) UI language, but switch the > locale to either de-DE German (Germany) for DD.MM.YYYY dates or to en-GB > English (UK) for DD/MM/YYYY dates, instead of fiddling with date acceptance > patterns in that weird way. There are different settings like * date format (DD.MM or DD/MM or MM/DD) * decimal separator (DOT or COMMA) * thousand separator (again COMMA or space...) * currency (euro, dollar, Indian Rupee etc.) How an average user would look for a country setting that has the right combination of all this? > > Once again, the users expect from the software that if something is offered > > to edit, the software will understand the input correctly at least if no > > information was altered. Here it is not the case. > Users should understand that when tweaking options derailing from presets > and forcing options that don't make sense it will have consequences. IMHO the *user* of the software does not have to have a level of a *developer* of such software just to use it. An average user wants just to deal with dates in some manner (like "DD.MM.YYYY"), and LibreOffice does not allow this in a *simple* way. > > When some settings in different independent places make this impossible, > > then the settings design may be improved. > Maybe best to restrict users' freedom and not accept such setting that > totally contradicts the locale's date pattern. Yes, or at least warn a user that such combination will not work as the user probably expects. ---- How about a following idea: if a date/time was formatted some way to edit the cell, first try to recognize the user input string using the same format. May be one can provide some extension rules like "HH:MM" can accept also "HH:MM:SS". If this does not work (the user has entered something else), then use "general rules".