SORT by months using functions

Today, we want to learn about sort­ing by months with the SORT function. 

Let’s start with writ­ing the SORT func­tion, with an array as the first argu­ment. In the sec­ond argu­ment we need to write 2, as we want to sort by months, which are locat­ed in the sec­ond col­umn. Now, we can close the func­tion (Fig. 1). 

=SORT(A2:C145,2)

Just like that the SORT func­tion sort­ed data by the Month col­umn. It’s impor­tant that the SORT func­tion does­n’t con­sid­er the cus­tom list. Excel does it, but the SORT func­tion does­n’t. Now. let’s have a look at how we can prop­er­ly sort by months using the SORT func­tion. First of all, we can­not use the SORT func­tion, but the SORTBY func­tion. In this func­tion, even if we change the sec­ond argu­ment to a month col­umn, we still get an ascend­ing sort­ing, from A to Z. It means that the sort­ing is based on the alpha­bet, not on months of the year. The thing is we can­not do the sort­ing based on argu­ments. We need to use the MATCH func­tion. It will look up a giv­en month in a list of months. In the third argu­ment we write 0, as we want to have the exact match (Fig. 2)

=MATCH(B2,$J$2:$J$13,0)

The MATCH func­tion starts the sort­ing from the top on the Month col­umn. As we can see in col­umn D, we have a prop­er num­ber for each month. The col­umn acts as a helper col­umn, how­ev­er, some­times we don’t want any helper col­umn, so we can cre­ate a helper col­umn in our for­mu­la. We can match each month from the Month col­umn (col­umn B) using the MATCH func­tion, where we need to write the lookup val­ue in the first argu­ment, which is our data col­umn. In the sec­ond argu­ment, we need to write the whole list of months on the right, then let’s write 0 as the exact match (Fig. 3)

SORTBY(A2:C145,MATCH(B2:B145,J2:J13,0))

It works. We see that the SORTBY func­tion found a prop­er num­bers for each month. It’s impor­tant that it sorts by months, not by years. How­ev­er, we can add the year as well. But, I want to show you some­thing else. First of all, if we don’t like the cus­tom helper col­umn list, we can just click F9 in the lookup array argu­ment to hard code the array list in our for­mu­la (Fig. 4)

=SORTBY(A2,C145,MATCH(B2:B145,{“January”;“February”;“March”;“April”;“May”;“June”;“July”;“August”;“September”;“October”;“November”;“December”},0))

We can even delete the cus­tom col­umn. As we can see, it works prop­er­ly. When we look at col­umn D, we can see #N/A error. It’s because the MATCH func­tion is try­ing to find months in the cus­tom col­umn cells which are emp­ty (Fig. 5)

=MATCH(B2,$J$2:$J$13,0)

Now, let’s focus on dates based on years and months. Some­times, our date in a dataset isn’t writ­ten only in one col­umn, but it can be sep­a­rat­ed into the year, month and day columns. We can cre­ate a full date from the data we have. We will try it in the Helper col­umn. First of all, we write =, then 1 com­bined with a month, com­bined with a year (Fig. 6)

=1&B2&A2

And just like that, we com­bined the 1st of Jan­u­ary 2022. Our date, how­ev­er, is text. We want Excel to under­stand it as a date, so we just need to add paren­the­ses and a 0 (Fig. 7)

=(1&B2&A2)+0

Excel mag­ic has just hap­pened! It changed text into num­bers. Now, to change the num­bers into dates, we need to go to the Home tab (1), then change the for­mat­ting (2) into Short Date (3) (Fig. 8)

Since we have dates based on the year and the month columns, we can sort them. Let’s write the SORTBY func­tion. In the first argu­ment we write the data col­umn, and in the sec­ond col­umn we have to com­bine 1 with the Month col­umn, then with the Year col­umn. When we close the paren­the­ses we need to add 0 to change text val­ues into num­bers (Fig. 9)

=SORTBY(A2:C145,(1&B2:B145&A2:A145)+0)

Now, we have only six Jan­u­ar­ies at the begin­ning, because we have have only six Jan­u­ar­ies in 2022. Next six Jan­u­ar­ies are in 2023. This way we sort­ed our data by months and years using a helper column.