Calc - Date sorting problem

English
#1

working with libre office calc for a while now and having a spreadsheet containing columns for name, birthdate, adress…for members of our organisation.
I try to sort the column of the birthdate by day of month in ascending order to use later in a newsletter. Date format for the column is set to MMM DD (Dec 31)
Problem: sorting is not consistant, as members are added over time, means the dates are not entered at the same time. And there lies the problem to get it sorted.

Example: entering Jan 01 on June 2, 2026 brings up 2026-Jan-01 when double clicking on the cell which contains visible Jan 01, according to the formatting.
Now, dates entered on say July 07 2023 with a date of Jan 10 would bring up 2023-Jan-10 by double clicking the cell containing visible Jan 10. This would result in a ascended sorted order by row of:
First row: Jan 10 (entered 2023)
Second row: Jan 01 (entered 2026)

For the past time I sorted the “wrong sorted” dates by hand but that is not how it’s suppose to be, or is it? :wink:

The sorting is including the year the date was entered with the entered date. See above.
Is there a way/step to exclude the year when entering a date? If so, where? If not, all sorting is not working as expected. Or can I exclude the year when sorting? Have not found any setting for that.

Here is my newest version for my Devuan system.

Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 4; OS: Linux 6.1; UI render: default; VCL: gtk3
Locale: en-CA (en_CA.UTF-8); UI: en-US
Debian package version: 4:7.4.7-1+deb12u12
Calc: threaded

I would appreciate any hints/help.
Thank you

#2

I think you should work with dates rather than fighting them. Create a new column and at the start enter the formula =MONTH(A2) where A2 is the column for birthday, press Enter then double click the bottom right corner of the cell to copy the formula down. Use that column to sort by Month.

If you want to see the month number in words you could create another column and in the first cell of that type =TEXT(A2;"mmm") or “mmmm” for full month. Copy down again but don’t use this column for sorting.

SortByMonth135378.ods (18.2 KB)

#3

thank you for the reply, but that will not work.
Did a internet search, (entering date without year) maybe I should have done this in the beginning :smiling_face: , and came over a suggestion not using text function instead of date function for formatting the cells. That is working as expected. :+1:

#4

And that is what I gave… The second part was only for human reading and I was clear it shouldn’t be used for sorting

#5

that creates too many unneeded columns. Using just text formatting instead of date formatting one just needs 1 column to be able to sort properly. The second column could be than the year.
From the sample below I just sort out by month and this will be sorted afterwards.
This way I will just have 2 columns, in the main spreadsheet (for birthdays) and in the new created sheet with just the monthly results.
test.ods (23.1 KB)

#6

PKG_SortByMonth135378.ods (25,3 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.