Description: At formatted columns without year digits AutoFilter doesn't work properly. It is no longer possible to use digital values to insert and edit dates. Steps to Reproduce: 1.1. I have some formatted file from 2018; see Dealwth sheet at bad_date.ods. It has two columns with formatted dates without years digits: Q and X(the X column is a link to column Q). These columns have AutoFlilter for them. When I try to make filter by dates for them, the filter don't expose years digits for them, thus they cannot be filtered correctly by dates taking into account the year. 1a) I can filter dates by the way.To do this, I convert the dates into a number format and apply a Standard Filter. For example for filtering May, 2018: Column Q > 43220 AND Column Q < 43252 See bad_date-2 sheet at bad_date.ods. I do this trick in a copy of the file so as not to spoil the main file. 1b) For version 6.4.7.2 AutoFilter works fine, see bad_date-3 sheet at bad_date.ods. 2. From version 7.6.3.2 something strange is happening with the dates inserting. Previously I could write in the Russian keyboard layout: 02.10. then press Enter and get the date in the current year => 02.10.2023. Moreover, in the Russian locale I could use the English keyboard layout to quickly set the date: 02/10/ then press Enter and get the date in the current year => 02.10.2023 or another formatted date. Today this method has stopped working. Moreover, full date entry does not work: 02/10/2023 or 02.10.2023 are not formatted as a date anymore. The only way left to enter the date is to enter the day and name of month for the current year or the day, name of month and year for other years: Dec 05 => 12/05/2023 Dec 05 2021 => 12/05/2021. This input method does not allow you to substitute dates in formatted cells. For example, in the table from the file bad_date.ods, in column A, you can substitute the current date in the drop-down window. In the English locale this formatting works, but in the Russian locale it does not work: numbers are substituted, but they are not formatted as a date! Actual Results: 1. Autofilter doesn't see formatted dates without year digits. 2. Entering date with digits only is impossible. Expected Results: 1. Autofilter must to see full dates when formatted cells doesn't contain year digits. 2. Entering dates with digits must to format cells as date formatted. Reproducible: Always User Profile Reset: No Additional Info: Version: 7.6.3.2 (X86_64) / LibreOffice Community Build ID: 60(Build:2) CPU threads: 2; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-US (ru_RU.UTF-8); UI: en-US Ubuntu package version: 4:7.6.3-0ubuntu0.22.04.1~lo1 Calc: threaded Version 6.4.7.2 doesn't has these troubles.
Created attachment 191349 [details] Formatted with dates columns A, Q and X at first sheet. Another two sheets contain screenshots. The screenshot in the bad_date-3 sheet was made for the Russian locale. Please don't let the Russian letters bother you. You need to see what dates look like for different numbered versions of Libreoffice in AutoFilter: with or without years.
3. Using the shortcut keys "Ctrl+;" You can insert the current date. In the Russian locale with an English keyboard layout, this does not work; It works with the Russian keyboard layout only.
(In reply to DerkMuncoster from comment #2) > 3. Using the shortcut keys "Ctrl+;" You can insert the current date. In the > Russian locale with an English keyboard layout, this does not work; It works > with the Russian keyboard layout only. I'm sorry - it was a mistake. Фnother program at my machine works with the same shortcut keys - IBUS. So everything is fine here.
Yesterday new updates arrived on Libreoffice. My Libreoffice has been updated to version: Version: 7.6.4.1 (X86_64) / LibreOffice Community Build ID: 60(Build:1) CPU threads: 2; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: ru-RU (ru_RU.UTF-8); UI: ru-RU Ubuntu package version: 4:7.6.4-0ubuntu0.22.04.1~lo1 Calc: threaded Thus, the errors described in paragraph "2." have already been corrected. The errors described in point "1." still remain. I wish the Libreoffice team success and that they fix all other shortcomings just as quickly.
Thanks for the report. Regarding the remaining issue 1, the change in behaviour happened in LO 7.2, I believe for bug 140968. Displaying the values in the list as they are formatted on the sheet was intended. Balazs, can you comment?
Created attachment 191643 [details] autofilter on column that only contains dates When starting from a new document, formatting the dates as "day-month" and omitting the year, I can filter by year by using the treelist view in the autofilter. My guess is that the sample file does not use such a date treelist view because there are cells in the column that don't contain a date (e.g. Q7). My take is this is "not a bug" because the column has mixed data and therefore LO needs to deal with the formatted data it has (and handle them as strings).
Created attachment 191774 [details] Screenshot from LO 6.4.7.2 and LO 7.3.7.2 for the SAME document. In the same document, the old version of LO shows the year in AutoFilter.
(In reply to Stéphane Guillou (stragu) from comment #6) > My take is this is "not a bug" because the column has mixed data and > therefore LO needs to deal with the formatted data it has (and handle them > as strings). Thanks for your reply. Please look at the third sheet in my file bad_date-2.ods, named bad_date-3. It shows two screenshots for the SAME DOCUMENT. LO 6.4.7.2 shows dates as a date with the year, but LO 7.3.7.2 does not show the year. I have attached a screenshot from the third sheet as a separate file to my report for clarity.
(In reply to Stéphane Guillou (stragu) from comment #5) > Thanks for the report. > > Regarding the remaining issue 1, the change in behaviour happened in LO 7.2, > I believe for bug 140968. > Displaying the values in the list as they are formatted on the sheet was > intended. > > Balazs, can you comment? Sorry for the late reply. For me this is also looks like "not a bug", the same reason what Stéphane wrote in https://bugs.documentfoundation.org/show_bug.cgi?id=158634#c6 (Thanks for looking into it deeply, Stéphane.) In the pop autofilter window we are using now the formatted values from a cell. In case of this example document there is a custom dell format for those cells: [>0]DD.MMM.;[<0]"";"нет". Therefor we are showing the filter values without the year. If you change the cell format (dates with years), it should show the filter values with years as well.
(In reply to Balázs Varga (allotropia) from comment #9) > For me this is also looks like "not a bug", the > same reason what Stéphane wrote in > https://bugs.documentfoundation.org/show_bug.cgi?id=158634#c6 (Thanks for > looking into it deeply, Stéphane.) > In the pop autofilter window we are using now the formatted values from a > cell. > In case of this example document there is a custom dell format for those > cells: > [>0]DD.MMM.;[<0]"";"нет". Therefor we are showing the filter values without > the year. If you change the cell format (dates with years), it should show > the filter values with years as well. In the old version of LO 6.4.7.2, the year is visible in AutoFilter. Because a date without a year is just how the date is visible in the cell. In the formula bar for each date you will see the full date with the year. For example: cell Q3: 28 Mar -> formula bar Q3: 28.03.2018. If you would can download LO 6.4.7.2 you could see full dates at AutoFilter at this file.
So the question is: why did the AutoFilter for LibreOffice, when viewing dates, start looking at the formatted value of the cell, and stopped seeing the true value of the cell, such as in the formula bar.
Created attachment 191782 [details] modified sample ODS to compare with or without strings coming from a complex format code Hm I think there might actually be something to fix here. See the attached modified sample document: - column Q is similar to the original data: it uses a custom date format that displays "нет" in some cases. The AutoFilter does not use the date trees for such formatted dates. - column R has the same data, but using a standard date format. Dates use the date trees, _even though the column has mixed data_: there are some strings in there (e.g. "string" and "да", highlighted in orange) but that doesn't stop the AutoFilter from constructing date trees for date data, including the year even when it is not displayed, and keeping the text data as is at the bottom of the list. So I agree that there is a bug, and that e.g. cell Q5 should be handled just like cells Q4 or R5. Balázs, do you think the issue stems from the format code complexity and use of a condition? For reference, the format code is: [>0]DD.MMM.;[<0]"";"нет"
Created attachment 191793 [details] autofilter's missing date tree with complex format code Stéphane Guillou (stragu), Balázs Varga (allotropia) To be correct, there is no Autofilter date trees at LO 6.4.7.2 for this file. I have LO 6.4.7.2 on another machine and I am able to test it. AutoFilter has only date strings for column Q. From this it was difficult for me to filter the Q column by date with the AutoFilter and before. For example to filter by "October 2018" I had to enter "10.2018" at AutoFilter's search bar(see attachment file). But it was enough for me. Therefore, I understand why you decided that this was not a bag because AutoFilter in LO did not work properly for complex format code all the same. So I think it must be fixed anyway. To be able to filter by dates I tested different office packs for this file: Callligra, Gnumeric, FreeOffice and OnlyOffice. Only OnlyOffice from them all exposes tree dates for column Q at AutoFilter.(OnlyOffice is very similar to Google spreadshits). So this means that such a possibility exists and it is possible to fix AutoFilter in LO as well.