Excel - How to arrange columns as per the chronological months

Solution 1:

The trick to good sorting is that Excel needs to know that it's a date.

You can convert the text in cell C3 (for example) to a date with this formula in C4:

=DATE(2000+RIGHT(C3,2),XLOOKUP(LEFT(C3,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},SEQUENCE(1,12)),1)

It looks long and unwieldy, but it's somewhat simple when broken down.

We're using the DATE function. This takes three arguments (separated by commas):

1. Year
2. Month
3. Day

The year is 2000+RIGHT(C3,2). This is just adding the right-most two characters from your text to 2000 to get the year. If you have dates before 2000, you'll need to adjust this part.

The month is XLOOKUP(LEFT(C3,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},SEQUENCE(1,12)). This is saying "Look for the first three characters in cell C3 in this list of months. Return the value from the same position (i.e. if Apr, the 4th position in the list), from SEQUENCE(1,12)." This SEQUENCE is just a list of numbers from 1 to 12. So we're converting the three-character month back to a number to use it in the DATE function.

The day is just 1. This can be any number less than or equal to 28, since all months have at least that many days.

So, for C3, the year is 2000+21, the month is Apr - i.e. the 4th month, so 4, and the day is 1. So we have DATE(2021,4,1).