Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

I want to sort by month

I pulled a group of my contacts into Numbers. I want to sort by birthdays. However, even though I formated the birthday to just show month/day, it takes the birth year into account.

I just want to sort my month & day. How can I do that?

MacBook Pro

Posted on Sep 8, 2012 8:25 PM

Reply
6 replies

Sep 8, 2012 9:19 PM in response to nduttonca

Sorting by Date will always include the year, if the sorted cells contain Date and Time values.


You can separate out the month (as a number) and Day (as a number) and concatenate them into a string (adding a leading zero for months from January to September and days from 1 to 9). These will correctly sort as text into the order you want.


If you need the Month names, just include the date cells displaying month and day as you already have them. But don't sort on that column.


Here's a formula to build the sortable string:


Birthday (as a Date and Time value) in column B, starting at B2. Formula goes in C2 and filled down to end of list.


C2: =RIGHT("00"&MONTH(B2),2)&" "&RIGHT("00"&DAY(B2),2)


Regards,

Barry


PS: Untested at the moment. Will check it later this evening when I have time.

B

Sep 9, 2012 12:11 AM in response to Barry

Tested, and working. Here are some enhancements.


As written above, the formula results can be used to sort and group the birthdays by month and sort by day within each month. The year of birth is ignored. Here is the top (fist 20 dates) of my test table. Column A was numbered to enable returning to the original order. Column B contains 120 randomly generated date of birth values. These are Date and Time values (with the time part set to 00:00:00 as they would be if the dates were entered manually). Column C is the key column in sorting. D, E and F show some possibilities in displaying the data.

User uploaded file

The first line below shows the original suggested formula for producing a column of data that can be sorted by birthday (month, then day) without regard to year. The second line adds the year to the string, resulting in lines sharing the same birthday being sorted from oldest to youngest.


C2: =RIGHT("00"&MONTH(B2),2)&" "&RIGHT("00"&DAY(B2),2)

C2: =RIGHT("00"&MONTH(B2),2)&" "&RIGHT("00"&DAY(B2),2)&" "&YEAR(B2)


Column C is used only for sorting, and may be hidden if you do not want to display the dates in this manner.


Sorted on column C:

User uploaded file


Column D shows one way of redisplaying the results using the name of the month, rather than its number. It also adds the age of the person on this birthday. Sorting on this column will give similar results to sorting on column C (with the second formula), except that those sharing a birth month and day will be sorted from youngest to oldest.


D2: =RIGHT("00"&MONTH(B2),2)&" "&RIGHT("00"&DAY(B2),2)&" "&MONTHNAME(MONTH(B2))&" "&DAY(B2)&" "&YEAR(TODAY())-YEAR(B2)&"yrs"

D2a: =RIGHT("00"&MONTH(B2),2)&" "&RIGHT("00"&DAY(B2),2)&" "&YEAR(TODAY())-YEAR(B2)&"yrs"


D2a produces the same result as C2, with the age in years on the birthday in the current year appended.


Sorted on column D:

User uploaded file


Column E displays only the month and day values for the birthday. The formula is a subset of the formula in column D. This column will correctly group the birthdays into months and sort the groups by day of month, but will not sort the months into the order they are found in the calendar. It should be considered a 'display' column, and used with column C as the 'sorting' column.


E2 =MONTHNAME(MONTH(B2))&" "&DAY(B2)


Column F displays only the 'age at this year's birthday' for each date in column B. The formula is a subset of the one in column D.


F2: =YEAR(TODAY())-YEAR(B2)&"yrs"


Each of the formulas is independent of the others and depends only on the values in column B (plus the value returned by TODAY() in the formulas calculating an age figure). The formulas for columns C or D may be used to sort the list in accordance with your needs, Those in columns E and F will not sort in calendar order.


Regards,

Barry

Sep 9, 2012 7:07 AM in response to Barry

Barry,


Thanks so much, I rarely have to sort the date column. When I looked at what info was in the cell it was the complete date & time. Trouble was I had the year for some and those I did not "it" used 1604. So those sorted first, then sorted by the rest.

I have printed your notes for me to learn how to do it this way the next time.


Thanks, I really appreciate that you took the time to answer.


Nancy

Sep 9, 2012 12:56 PM in response to nduttonca

Hi Nancy,


Jerry's suggested formula was my first thought as well, but between the thought and putting it to 'paper', I made the assumption you'd also want to sort by date within each month, hence my initial formula.


The rest was added while I was checking that my initial answer had been entered correctly—something I usually do before posting, but that I didn't have time to do in that order in this case.


Regards,

Barry

I want to sort by month

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.