Description: I have number of document where I use date values in fields. I need dates displayed in DD.MM.YY. format so I set that so. But, LibreOffice always insisted to enter values in MM/DD/YYYY format. That means when editing value I have to type in MM/DD/YYYY but it is then displayed as DD.MM.YY. That was until the latest update to version 7.5.1.2. Now when I edit date, it switches to MM/DD/YYYY and let me edit but value is not recognized as valid date any more. Now I have to enter date as DD.MM.YYYY. to be recognized as date. It is the displayed as DD.MM.YY. This happens only if I edit date value which was already entered before LibreOffice update (which is in my case very often). Suddenly already entered value becomes invalid until i reformat it. I use LibreOffice for decades and I have number of documents. This is very annoying odd behavior. Actual Results: Date entered as DD.MM.YYYY is not recognized as correct date value Expected Results: Date entered in any appropriate date format should be recognized as such. Reproducible: Always User Profile Reset: No Additional Info: In years I learned to live with requirement that I have to enter date in odd and illogical format and use formatting to display it properly, but now that odd format I was forced to use, does not work any more and I have to edit all date fields in all documents using now format (again, different than one I actually need to be displayed) to make it work again.
Thanks for the report, Pedja. Please paste here the information copied from Help > About LibreOffice, as these default formats often depend on the locale you are using. Thank you!
Menu Tools > Options > Language Settings > Languages > Date acceptance patterns. That field changes depending on Locale settings too. At any rate, it can be manually modified. Any of these fields in the Options dialog are different than / independent of the cell format ([CTRL]+[1]). BTW, I think this is not the first report about some changed behavior in recent versions when introducing Dates and/or when loading files that already contain Date cells (that were saved by prior versions of LO).
See also bug 152877.
Version: 7.5.1.2 (X86_64) / LibreOffice Community Build ID: fcbaee479e84c6cd81291587d2ee68cba099e129 CPU threads: 12; OS: Windows 10.0 Build 17763; UI render: Skia/Raster; VCL: win Locale: en-US (sr_RS); UI: en-US Calc: CL threaded
(In reply to ady from comment #2) > Menu Tools > Options > Language Settings > Languages > Date acceptance > patterns. I checked this. Content was D.M.Y;D.M. and it was red colored (i guess invalid value?). I did not enter such value, I believe it is set as default on first install. I had to remove D.M. part to make it white and after I set it to be D.M.Y;M/D/Y I was able to edit dates in MM/DD/YYYY format
(In reply to pedja from comment #4) > Locale: en-US (sr_RS); UI: en-US (In reply to pedja from comment #5) > D.M.Y;D.M. > > and it was red colored (i guess invalid value?). > > I did not enter such value, I believe it is set as default on first install. > > I had to remove D.M. part to make it white and after I set it to be > > D.M.Y;M/D/Y > > I was able to edit dates in MM/DD/YYYY format Maybe there was some change in the default locale settings in some recent version, in particular for Date format(?). @pedja, * Are these documents ods? Or instead, are we talking about some other format such as xls/xlsx? The reason for me to ask is bug 154311, which shows clearly that some changes were made after 7.4. regarding dates. Whether such change is somehow related to this bug, I don't know yet. * Any chance you could attach a sample document that is/was failing for you (after deleting any private/confidential info)?
Created attachment 186121 [details] DATE test
(In reply to ady from comment #6) > * Are these documents ods? Or instead, are we talking about some other > format such as xls/xlsx? Yes, ODS. > * Any chance you could attach a sample document that is/was failing for you > (after deleting any private/confidential info)? I uploaded example, but I think it is not the same as after i updated Date acceptance pattern, I amunable to set it back as it was. It was D.M.Y;D.M but that is not accepted. I entered D.M.Y;D.M. to make example. The first row is entered as MM/DD/YYYY The second row is entered as DD.MM.YYYY
In a hunch, I am CC'ing Mike Kaganski. Maybe he happens to know what's going on, but even if he doesn't, maybe he will be able to point to someone who might. (In reply to pedja from comment #8) > I uploaded example, but I think it is not the same as after i updated Date > acceptance pattern, I amunable to set it back as it was. > > It was D.M.Y;D.M but that is not accepted. I entered D.M.Y;D.M. to make > example. This is what seems to be wrong. For example, when the Locale is English (USA), the default Date acceptance pattern is "M/D/Y;M/D". I am not seeing the reason for Calc to flag "D.M.Y;D.M." as wrong with a red color except maybe the very last dot after the last "M". But even if that's the problem, why now and not before? > > The first row is entered as MM/DD/YYYY > The second row is entered as DD.MM.YYYY In attachment 186121 [details] from comment 7, cell A1 (that was supposed to be MM/DD/YYYY according to your comment 8) is not really a date but text – tricky, I know. This seems to match the Date acceptance pattern of "D.M.Y;D.M." (with or without the latest dot/period/stop) which doesn't include MM/DD/YYYY (for this example, according to your comment 8). Using my settings, I see that cell A1 starts with an apostrophe. I'm not sure that you are seeing it under your Locale settings, or that you introduced it intentionally; probably not. This reminds me of another very tricky and unclear bug report involving dates and apostrophe (not seen by the original reporter) and Date acceptance pattern too: https://bugs.documentfoundation.org/show_bug.cgi?id=148747#c39 – just in case anyone is interested, I would suggest only reading after c#35 and not before it, because it could be more confusing than anything else.
D.M.Y;D.M format is what i found out set, and it was coloured red. I was unable to recreate it, but after i added dot and entered D.M.Y;D.M. it was accepted. In my original document all fileds are dates. Thez are used in date calculations, and those calculations break if I edit date filed and try to enter date in MM/DD/YYYY format, which I was forced to do in previous versions and onlz that waz dates worked. Since update calcualtions worked until i tried to edit date fields. It dod not accept MM/DD/YYYY format, but I had to enter dates in DD.MM.YYYY format. After I changed D.M.Y;D.M to D.M.Y;M/D/Y I was again able to edit date in MM/DD/YYYY format. That filed that ends up string instead of date is probably because of D.M.Y;D.M. setting. It does not accept MM/DD/YYYY as date so it saves it as string. that is probalbz what happens in already existing date fields. When i edit them and try to keep MM/DD/YYYY which is already entered (and recognized as date) it is converted to string and everything depending on it fails. I usually edit date just to alter year part, but after this update I have to type in whole date in new format to be recognized as date.
In a locale that uses the '.' dot decimal separator you obviously can not have a D.M date acceptance pattern. D.M. would work. Not a bug.