Google Sheets – How to Sort by Month and Day but Not Year

google sheetsgoogle-sheets-datessortingworksheet-function

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.

Best Answer

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:

The FILTER function will filter for all rows that have something.
The MONTH and DAY functions will extract the respective values from the date.
The TEXT function will convert the value from point 2 into a pre-formatted STRING.
The ARRAYFORMULA function will apply all the above to the complete column (skipping the header).
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

Related Solutions

Google Sheets – Nested IF Statement with Same Second Part

Assuming you have "BANK" in I3:I20 (as opposed to "AMEX") for bank payments, then I believe you can achieve your goal by reversing your logic (equal to "BANK", rather than not equal to "AMEX")...

Change the formula in F24 to:

=arrayformula(sumif(H3:H20 & I3:I20,E24:E33 & "BANK",D3:D20))

This will then populate cells F24:F33 with the appropriate values. & is the string concatenation operator, so this effectively compares "1BANK" with "1AMEX" (for instance).

Google Sheets Date Format – How to Format Dates in Spreadsheets

Formatting is not the answer, because it doesn't affect the underlying values. You need to make the values updatable instead.

One way is to make a reference-data place somewhere in one of your sheets (or maybe add a new sheet just for that), and put the year value in there.

Then in the first cell where you want the date values to appear, use this formula:

=date(B1,01,01) except replace B1 with the value of the cell where you put the year value.

In the cell immediately above or beside it, say =D1+1 except replace D1 with the reference to the cell where you put the first forumula.

Then to make a new file for next year, just save-as and change the one reference value.