Sort Date field by Day, Month -- ignoring Year (bday sort)

Discuss the spreadsheet application

Sort Date field by Day, Month -- ignoring Year (bday sort)

Postby bobgriner » Mon Mar 14, 2011 3:43 pm

I'm trying to sort a long list of people who have their DOB listed in the Calc sheet. I'm simply trying to be aware of upcoming birthdays, therefore I only need the month and day.

I don't want to go through some big reformatting or changing of the existing info structure...I just want to sort by Month and Day.

Anyone know an easy sort function in OO 3.2.1? I'm a new user.

Thanks,
Bob
OpenOffice 3.2.1 on MacOSX 10.6.6
bobgriner
 
Posts: 4
Joined: Mon Mar 14, 2011 3:40 pm

Re: Sort Date field by Day, Month -- ignoring Year (bday sor

Postby Villeroy » Mon Mar 14, 2011 3:47 pm

A calculated field:
=TEXT(A2;"DD-MM")
=TEXT(A2;"MM-DD")
to be copied down and used for sorting.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27226
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sort Date field by Day, Month -- ignoring Year (bday sor

Postby acknak » Mon Mar 14, 2011 4:30 pm

Villeroy wrote:A calculated field: =TEXT(A2;"DD-MM") to be copied down and used for sorting.

Or maybe =TEXT(A2;"MM-DD") ;-)
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Sort Date field by Day, Month -- ignoring Year (bday sor

Postby bobgriner » Wed Mar 16, 2011 5:21 pm

Sadly, I have no idea what the info you posted means. I know how to use the "sort" menu, I understand putting formulas in the "input line," but I think you're speaking "short hand" to someone that doesn't know what you mean?

Help me out with:

TEXT (A2;"MM-DD")

This seems to be a formatting function of a date, but I'm not sure what to do with it. The statement "copied down and used for sorting" isn't clear to me.

Thanks for your help! I'll get there eventually, Bob
OpenOffice 3.2.1 on MacOSX 10.6.6
bobgriner
 
Posts: 4
Joined: Mon Mar 14, 2011 3:40 pm

Re: Sort Date field by Day, Month -- ignoring Year (bday sor

Postby bobgriner » Wed Mar 16, 2011 5:29 pm

I went into the formatting section and reformatted the text column to "only" list MMMM DD. However, when I sort it still doesn't sort appropriately. Although my "sort by" is DOB first, the result doesn't give me anything logical. It didn't seem to sort that column at all...not even simply putting the months in Alpha order....or the dates in Numerica order?

Hmmmm?
OpenOffice 3.2.1 on MacOSX 10.6.6
bobgriner
 
Posts: 4
Joined: Mon Mar 14, 2011 3:40 pm

Re: Sort Date field by Day, Month -- ignoring Year (bday sor

Postby acknak » Wed Mar 16, 2011 6:09 pm

Here's an example sheet: look at the column "BD Sort". The formula there takes the actual birth date value and produces a text value containing the birth month and day. This text value is suitable for use as the sort key: click on any cell in the "BD Sort" column, then click the "A..Z" sort button on the toolbar.
Attachments
bday_sort.ods
(9.84 KiB) Downloaded 780 times
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Sort Date field by Day, Month -- ignoring Year (bday sor

Postby bobgriner » Wed Mar 16, 2011 6:17 pm

Perfect! Got it -- thanks so much.

Shouldn't there be an "easy" way to do that with the simple "sort" function? I'm not the only one on the planet that want to sort a date column simply by the month and day am I?

Thanks again....that was great support and help!
OpenOffice 3.2.1 on MacOSX 10.6.6
bobgriner
 
Posts: 4
Joined: Mon Mar 14, 2011 3:40 pm

Re: Sort Date field by Day, Month -- ignoring Year (bday sor

Postby Villeroy » Wed Mar 16, 2011 6:22 pm

A database would provide easy ways to do this. Nobody uses databases and spreadsheets are too overloaded with non-mathematic features already.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27226
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: RPG and 23 guests