Apache OpenOffice Community Forum

I have data exported into xls with "time time cancelled prior to" and the data is in " 01 days 13 hours 10 min "

I need to sort longest to shortest but the data sorts 0, 1, 10, 100, then to 11,... 2, 20, etc.

I have replaced the words days, hours with colons decimals, and formatted the cells every possible way without changing the results.

Anyone have a way around this?

The whole column is text. Text sorts alphabetically.
In this case, alphabetical sorting would be OK if the contained numbers included leading zeroes as in "03 days 07 hours 08 min"
If you would install LibreOffice instead of OpenOffice (which is walking dead), you could switch on "natural sorting" which might do what you expect in this case.
https://libreoffice.org

To do this properly in OpenOffice Calc, I'd use helper columns to make the "time" into a proper hours:minutes, then sort using the helper column as the sort key.
An alternative would be to use Text to Columns, then sort using three sort keys.
If your current was in column A, then B would be the number of days, C -> the word "days", D -> number of hours, E -> the word "hours", F -> number of minutes, G -> the word "minutes"
Then sort everything using column B as your first sort key, D as your second, and F as your third.

I just tried with LibreOffice Calc and "natural sorting". The result is:
1 days 7 hours 33 minutes
2 days 5 hours 11 minutes
3 days 1 hours 2 minutes
3 days 13 hours 33 minutes
19 days 11 hours 3 minutes
29 days 3 hours 1 minutes
30 days 12 hours 22 minutes

Perfect. Thanks