Ask Your Question

How to filter days of the week (no date) ascending by day (not alphabetically)

asked 2019-04-09 04:53:28 +0200

5EBI Production gravatar image

Hello. We are a multicultural community radio station based in Adelaide, South Australia. I hope you can help...

In a spreadsheet, I am using the cell format DDD HH:MM (example: Mon 12:00 ; Tue 13:30 ; etc...) for one column. That's the day and time a programme is going to air. It is the same every week so there isn't a specific date.

I have applied a filter over all the columns of that document and it works very well with the other columns, but when I select "ascending" in this column, it groups the days together but it does so alphabetically (Fri; Mon; Sat; etc...) instead of the order of the days (Mon; Tue; Wed; etc...).

With the other columns of this spreadsheet, the ascending function works well (programme name; language; etc..), so we can look at programmes alphabetically or languages grouped together alphabetically.

But how can I make the "day time" column order itself by day, not alphabetically?

Thank you!!

edit retag flag offensive close merge delete


I cannot understand how you achieve to have "Mon", "Tue" etc. without having in fact a date in your cells. Just to add "Mon 12:00" into a cell does not mean that the cell format has any effect, since it is just a "string" and strings are sorted alphabetically.

Opaque gravatar imageOpaque ( 2019-04-09 08:32:14 +0200 )edit

I formatted the cell using the code DDD HH:MM, using the 'format cell...' dialogue from right clicking the cell, then the 'Numbers' tab, selecting the 'Date' category and the 'Fri 13:00' category, which shows 'DDD HH:MM' in the 'Format Code' box (User-defined)... Does that clarify things? (I am not sure if I can include a screen grab here...)

5EBI Production gravatar image5EBI Production ( 2019-04-09 09:21:31 +0200 )edit

No - it does not clarify anything and I think you did not get my comment right, because - as stated above - you can format the cell whatever you like. It does not matter if you just add "Mon 12:00" into a cell, since it is not recognized as a date but is still a "string". Just make a test using a cell format as Number and add "This is a string" to the cell. Now check again the Format Code - it shows that it is a Number but the text itself tells what it really is. The format has no real effect and is ignored - The Format Code does not reflect a type conversion, it is a "what should be there"-setting. The same happens with your date input. It is not a date in terms of internal data structures of LibreOffice but a string (By the way: Libreofffice format ...(more)

Opaque gravatar imageOpaque ( 2019-04-09 09:38:34 +0200 )edit

Okay. So obviously I did it wrong. Thank you. Now, my query is: can I have cells displaying a day of the week and a time - no actual date like '21 march 2019'; just the day of the week and a time - and be filtered 'ascending' by order of the days of the week (preferably starting with Monday, but not necessarily) instead of alphabetically? Can this be done and if yes, how?

5EBI Production gravatar image5EBI Production ( 2019-04-09 10:55:20 +0200 )edit

Can you upload your file?

gregors15 gravatar imagegregors15 ( 2019-04-09 11:08:34 +0200 )edit

4 Answers

Sort by » oldest newest most voted

answered 2019-04-09 11:31:42 +0200

Opaque gravatar image

updated 2019-04-09 11:32:08 +0200

No - you cannot have a day + time it in a single cell and expecting that it is recognized as a date (for LibreOffice locale date acceptance patterns see Tools -> Options -> Language Settings -> Languages in category Language Of) and thus sorted in natural order. In addition - at least for my gut feeling - you are mixing the concepts of 'filter' and 'sort' (I find it a bit confusing that filter dialog shows "Sort De-/Ascending" entries). If I'd need to do your sheet, I'd introduce two columns - One headed "Day" and the other one headed "Time". Now add "Mon", "Tue" etc under colum "Day" and to sort use Data -> Sort .. and under options use Custom sort order to get what you want:

image description

edit flag offensive delete link more

answered 2019-04-10 23:21:19 +0200

updated 2019-04-10 23:22:04 +0200

For correct sorting of column with dates/times, you need to have these dates/times to be represented as values, not text strings. Dates and times in Calc internally are represented as numbers (dates) and fractions of the day (time), so you need to correctly prepare data for sorting. For example, today’s noon2019/04/10 12:00 for Calc is a value of 43565.5 It looks like your column has just text strings, not real date/time values, so that is why sorting is performed in alphabetical order, not in numerical one. You can play a bit with values to learn - create new sheet, insert some random values with decimal part in A column, change cell format to YYYY-MM-DD HH:MM or DDD HH:MM and then perform sorting, the result should be as you expected. You can format cell to display weekday, date and time in one single cell, but in first place you need to have this cell to hold correct value, not just text.

edit flag offensive delete link more

answered 2019-04-15 01:37:52 +0200

5EBI Production gravatar image

I am sorry but I cannot make any of this work... As suggested by gregors15, I am uploading my file...

All I would like is to have all the programmes on a specific day bundled together and in order of time.

Thank you!

C:\fakepath\Pre-records CODES.xlsx

edit flag offensive delete link more


Sorry... To make it clearer; but then I need to be able to bundle them alphabetically by Code or by name at other times....

5EBI Production gravatar image5EBI Production ( 2019-04-15 01:43:12 +0200 )edit

answered 2019-04-15 08:07:17 +0200

Cookievore gravatar image

updated 2019-04-15 08:08:33 +0200

According to what @SM_Riga said, you could do like this: before you start entering data, the date cell should be empty, choose user defined format by right click > format cell > date > NN HH:MM, now you can and have to enter a valid date like 15.04.2019 07:58, with hitting enter it will render to Mo 07:58 although it still contains a valid date. Now it's a date value, it lets you sort this very easy.

C:\fakepath\Unbenannt 1.ods

edit flag offensive delete link more


I would have suggested to use STRG-; oder STRG-SHIFT-; as shortcut to insert the current date value, but this still does not work. At least with DE keyboard.

Cookievore gravatar imageCookievore ( 2019-04-15 08:21:53 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-09 04:53:28 +0200

Seen: 120 times

Last updated: Apr 15