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

Solution 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!

Solution 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.