Sort by Month then by Day (ignore year when sorting)

Question

Level 1

4 points

Sort by Month then by Day (ignore year when sorting)

I am attempting to create a spreadsheet to help me remember when to send cards to family for birthdays and other dates.

When I sort ascending it sorts the months alphabetically. I want to sort the "Birthdate" column from January 1st to December 31st.

I have added a column "Day of Year" and concatenated the numerical month and day into the column. When I sort ascending it sorts the months, but not the days.

There has got to be an easier way of doing this. Any suggestions would be helpful.

TIA,

Joke

Posted on Jan 14, 2023 11:05 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

36,092 points

Jan 14, 2023 6:39 PM in response to JokeApple

You could consider adding a sort column that calculates the fraction of a year from the beginning of the year and sort on that, hiding the column if you want after you've set it up:

In D2, filled down:

=YEARFRAC(DATE(YEAR(B2),1,1),B2)

More on YEARFRAC here:

YEARFRAC - Apple Support

SG

Reply

Answer 2

Ralf-F

Level 4

2,717 points

Jan 14, 2023 2:52 PM in response to JokeApple

Hi Joke,

sorry but Numbers is sorting correctly.

1-24 is smaller that 1-6!

The first 2 digits are identical and in the 3rd digit the 2 is smaller than the 6, therefore the sorting is done correctly.

It will sort the 1st digit first, then the 2nd digit and so on.

Here a solution for your task

Formula for C2=CONCAT(IF(MONTH(B2)≤9,0,""),MONTH(B2),"-",IF(DAY(B2)≤9,0,""),DAY(B2))

The IF function will add a 0 as soon as the month / day is only one digit.

Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.

Hope this will solve your question, please let me know if this worked for you or if something in unclear.

Regards Ralf

Reply

Answer 3

Badunit

Level 7

28,084 points

Jan 14, 2023 6:51 PM in response to JokeApple

Your "day of year" column could also be created as =DATE(1,MONTH(B),DAY(B)) with the cell formatted to show only the month and day. This formula makes all of them have the same year so they will sort as you wish. Any year will work, I chose year 0001.

sorted:

Reply

Answer 4

JokeApple Author

Level 1

4 points

Jan 14, 2023 10:46 PM in response to JokeApple

Wow! All 3 solutions worked! Thank you guys this has been a big help!!

I'm hoping this will help someone else like it helped me.

Below is my thoughts of your solutions and the chart for future reference:

Ralf-F: Most correct (You're right, computers always start at zero!)

SGIII: Most outside the box (looks complex but actually very simple!)

Badunit: Most aesthetic (The cleanest looking and also simple)

Reply