Sorting Google spreadsheet by month and day but not year

Question

I made a Google spreadsheet with a list of birth dates, anniversaries, etc. that I would like to sort by when they occur in the year, not when they first happened.
If you sort by the date normally, it will put the oldest year first.
I'm looking for a way to sort these only by the month and day, so I can get a quick view of what's coming up in what order.

So far, I attempted to make a formula referencing the date column that only shows the month part and the day part, like so:

=month(C2)&"-"&day(C2)

This almost works, except since the values are appearing as text and without a leading 0, it sorts Oct, Nov and Dec right after Jan (since they all start with 1 and are therefore "lower" than 2, 3, etc).
Anyone have a better way to sort this automatically or know a formula function to correct mine?
Simply adding the leading zero to single-digit months would be my way, but I'm open to other options.

Answer 1

Ultimately, you need to do what you already predicted: adding a leading zero. The TEXT function will make that happen like this:

=TEXT(A2, "00")

I've created a formula that takes on the complete column, filter for empty cells, brings together the MONTH and DAY and SORTs the lot.

Formula

=SORT(ARRAYFORMULA(TEXT(MONTH(FILTER(A2:A;A2:A<>""));"00") & " - " 
 & TEXT(DAY(FILTER(A2:A;A2:A<>""));"00"));1;1)

Explained

Here's a break-down description of the formula:

1. The FILTER function will filter for all rows that have something.
2. The MONTH and DAY functions will extract the respective values from the date.
3. The TEXT function will convert the value from point 2 into a pre-formatted STRING.
4. The ARRAYFORMULA function will apply all the above to the complete column (skipping the header).
5. The SORT function will sort the result, given by the ARRAYFORMULA.

The SORT function allows for sorting. If you do that via a column sort, the entry of the ARRAYFORMULA is taken into account as well and gets re-positioned, causing mayhem.

Screenshot

Example

See example file I've created: sorting dates as text

Answer 2

Sounds like you just want a helper-column that you can manually sort. I'm a lazy person, so I would get annoyed at having to copy & paste a formula downward every time I run out or I need to add new dates. I suggest you use Jacob's array formula, but just remove the "Sort" part of it.

Here is a version I wrote of an array that you can write directly into the header row.

={"Helper Column";ArrayFormula(IF(A2:A<>"",TEXT(A2:A,"mm - dd"),""))}

The benefit of writing the array formula into the header row, is that when you perform a manual sort, you won't accidentally move the array causing "weird results."

The curly brackets allow you to list explicitly an array example {A,A} would be a 1 x 2 array, where {A;A} would be a 2 x 1 array. That way you can simply wrap curly brackets around Jacob's formula (unsorted) and just explicitly list your Column Header as "Helper Column" followed by a semi-colon (;) before jacob's formula inside the curly brackets.

Note: this works in Excel too, but it's just a little more difficult to deal with arrays in Excel. As you have to preselect the range you are about to write an array into before writing the array and the "Arrayformula" formula does not exist in Excel.
Instead you need to hold CTRL+SHIFT+ENTER to complete your formula as an array.

Answer 3

A shorter version:

=ArrayFormula(if(A2:A>0,sort(left(text(A2:A,"MM-dd"),5)),""))

However, as I think for the other two answers (so far), since driven by the 'A' column the results, dynamic in that they will automatically accommodate further entries in ColumnA, cannot be applied directly as a sort key to re-order the contents of ColumnA.

So, although indicating the sequence of birthdays, not much use for linking that sequence to, for example, the names of the individuals concerned, without further processing.

Or put another way, a column is sorted rather than a sheet.

IF
LEFT