How to correctly sort calendar months "mmm" with AutoFilter?

Question

I need to use filter to sort calendar months in mmm format.

Data > Sort > Custom will do it but I cannot make filter work. Months are in a column eg SEP, APR, JAN, APR, etc.

Filter just does an alphabetical sort so it puts all the APR first.

Answer 1

It will sort correctly if they are actual dates. Say we start with:

and we format the dates as "mmm", we now have:

Using AutoFilter we sort the column:

As you see Jan comes first!

Answer 2

As mentioned in other answers, you can create a helper column with actual date values and sort based on that. I would argue the easiest method is to use =DATEVALUE(A2&"1") where A2 holds the month name in text. This'll give you the first day of each month in the current year but that's adequate for just sorting purposes.

However, it's possible that you don't have the ability or rights to do that. In that case, you can add a custom sort list in Excel. Once you've opened the sort dialog box, choose Custom List... in the Order drop-down.

In the dialog window that opens, click on NEW LIST on the left and then create your list on the right, separating each item with a line break. Once you're done, click Add.

As you can see, I've already created short and long versions for both days of the week and months of the year. Once you're done adding whatever lists you want, select it in the list on the left and click OK.

Answer 3

We’re struggling to understand your question.  I guess that you’re upset that, when you enter month names as text

                                                               

and set a filter on the column, and click on the filter triangle:

                                               

the values are listed in alphabetical order.

One solution would be to create a helper column, with B2 equal to

=TEXT(MONTH(DATEVALUE(A2 & "1")), "0# ") & A2`

and dragged down:

       

• A2 & "1" turns the bare month name (JAN) into something that looks like a date (JAN1).
• DATEVALUE(A2 & "1") converts that date string into a numeric date/time value.
• MONTH(DATEVALUE(A2 & "1")) computes the month number (1–12) from that date value.
• TEXT(MONTH(DATEVALUE(A2 & "1")), "0# ") formats that number with a leading zero (as necessary) and a trailing space.
• TEXT(MONTH(DATEVALUE(A2 & "1")), "0# ") & A2 concatenates that two-digit number with the month name.

Now, if you filter that column, the months are listed in the correct order:

                                               

You can put this helper column in column Z, where it will be out of sight, or you can hide it, unhiding it only long enough to change the filter.