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

[5EBIProduction]

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!!

[anon73440385]

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.

[5EBIProduction]

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…)

[anon73440385]

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 Code “DDD” can only determine the day if it has a date).

[5EBIProduction]

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?

[gregors15]

Can you upload your file?

[anon73440385]

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:

[SM_Riga]

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.

[5EBIProduction]

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!

Pre-records CODES.xlsx

[5EBIProduction]

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

[Cookievore]

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.

Unbenannt 1.ods

[Cookievore]

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.