Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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:

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

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.

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

share|improve this answer
    
Hmm. That seems promising but I'm getting some weird results. I think it's because your sample is only the dates, and does not include the other columns I have. It did what was expected when I added the formula, but when I added a new date everything got out of whack. I'm not familiar with the auto-sorting function you used, so I'm not sure where it needs to be modified (yet...). –  techturtle Mar 6 '13 at 22:06
    
Well, I've got more playing to do if I want the auto-sorting to work, but since that wasn't part of my question, I modified your formula to this =TEXT(MONTH(C2),"00") & "-" & TEXT(DAY(C2),"00") which lets me sort it exactly as I was hoping to, so I'm accepting this answer. Thanks for the info! –  techturtle Mar 6 '13 at 22:32
    
@techturtle Thanks for your feedback. I've added more explanation to the answer, as to the sort function. The ARRAYFORMULA allows for some sort of automation, because it will work on a complete column, no matter how many rows are added. Good luck!! –  Jacob Jan Tuinstra Mar 7 '13 at 6:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.