Text to column solving hour filter problem : how is it working?

[Cortez1]

Thanks everyone for looking into it and taking time explaining me a few things!

I’m not sure to understand why it’s not working, but you seem all pretty sure it’s normal that it doesn’t the way Calc is designed. From my point of view, it seems possible to convert the values & conditions to a numerical value (it was formatted the other way), compare them to filter, and put it back to the desired format. But I must be missing something, format seems to be a little more than a simple display option.

Anyway, as I said in my original post, I actually have a solution, but was unable to understand why it worked. I just have to select my column => “Data” => “Text to column” => Field : Column type “Text”.

Then, it changes the format to Text AND keeps the HH:MM:SS display. I feel like it checks the steps presented by Lupp?

And with that, the filter perfectly works, and I can sort it without problem.
musics_working.ods (49.0 KB)

I’m sorry I didn’t notice “Text to column” changed the format to Text I was convinced I checked it before posting, but I must have forgotten to or made a mistake by checking the wrong document.
Now that I see the format changed, I understand why it works: it simply filters/sorts “alphabetically” (not sure if it’s the proper term since we have digits, but that’s the idea).

So I guess it’s a good workaround to the problem, even though it does not solve it. And if you really need the data as number, you can double the column, one with the data as number and one with the data as text.

I actually never used LibreOffice Base, so I’m going to trust you that it works :o But I definitly need to learn to use it too.

[Villeroy]

Text2Columns is a quick method to convert wrongly imported text data into valid numeric values. This is what they discuss in topic Calc: Sort on formatted time column (your link in OP).
You convert the numbers into text. Text filtering is even more complicated.

I uploaded a slightly enhanced database. This is fool proof to use. You can enter minutes 0-999 and seconds 0-59 as filter criteria and as a replacement for the time values.

Have a closer look at my spreadsheet upload which makes use of advanced filtering. You enter valid, numeric criteria into cells, the values are referenced by formulas in the criteria range, the criteria range is a named one and flagged as “Filter” so it appears in the advanced filter dialog. As far as I can tell, this method works properly if both values are given. Now it works with missing criteria values.