How to Sort Dates in Excel by Month (4 Ways)

Situations may arise when you need to sort dates by month, year, or many more. Today we are going to show you how to sort dates in Excel by month. We are going to use Excel 2019 (and a little bit of Excel 365). You can use your preferred Excel version.

Before diving into the big picture, let’s get to know about the workbook, which is the base of our examples.

We have a basic table of three columns: Movie Name, Genre, Initial Release. Here the Initial Release contains the dates. Using this table we will sort dates by month.

Note that this is a basic table with a simple scenario, in real cases, you may encounter a much larger and complex data set.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Sort Dates by Month in Excel

You can sort dates by month in several ways. Let’s explore them.

1. Excel Tools with MONTH Function

Having a mere basic knowledge of Excel, you may know that Excel provides feature tools to sort data. We will make use of that. Along with that, we will use a function called MONTH. 

The MONTH function extracts the month from a given date as a number between 1 to 12.

serial_number: The date of the month you are trying to find.

To know more about the function please visit the Microsoft Support site.

Let’s start our approach. First of all, we need to introduce a new column, where we will store the value derived using the function.

Here we have added a column Month. In this column, we will store the result of MONTH. 

Write the MONTH function for the first cell. Similar to the other Excel function we can insert the cell number of our data inside the function.

Here we have provided the Cell Reference of the date. And the function provided the month number; it’s well known that November is the 11th month in the English calendar

Write the function for the rest of the rows or simply exercise the AutoFill feature of Excel.

Here we have exercised the AutoFill here.

To sort, select the column by which you want to sort.

We want to sort by month, so we have selected the column of the Month presented in numbers.

In the Data tab, you will find the Sort & Filter section and there is an option A to Z. 

Click the option.

Selecting a single column from a table will lead to a Sort Warning dialog box. Usually, you need to select Expand the selection there and click Sort or hit ENTER.

We have found it sorted by month.

Here we have sorted in ascending order. If you want to sort in descending order, then click Z to A from the Sort & Filter section of the Data tab.

Selecting the entire data, we have clicked Z to A. 

This provided the months in descending order.

2. Excel Tools with TEXT Function to Sort Dates by Month

Instead of the MONTH function, we can use another function called TEXT. 

The TEXT function returns a value in a given data format. Usually, it converts a number to text in a number format.

The syntax for the TEXT function

value: The data value to convert.

format_text: Desired format to use.

To know more about the function please visit the Microsoft Support site.

To extract the month number our format_text will be “MM”. So the formula will be

Do you know why we have used MM?

When you write MM, Excel understands you are commanding about a month. This is the least way of representation. And the least representation we make of month is the month number. Excel does the same.

Write it in Excel.

We have used the Cell Reference of our date value. The formula provided the month number of our given date.

Do the same for the rest of the rows or use AutoFill. 

Now the things will be similar to the previous, select the column and choose the ascending or descending order depending on your needs and preference.

For example, we are choosing ascending order (A to Z) here.

The Sort Warning dialog box will pop up in front of you. If you explicitly want to sort your selected cells only, then select Continue with the current selection. 

Otherwise, select the Expand the selection. We are selecting that for our example.

We have found the dates sorted by month in ascending order.

Note that you can use mm in place of MM in the formula.

3. Use of Custom Sort

In the earlier approaches, we needed to use an extra column. If you are tired of that then we can introduce another method here.

We will do Custom Sort here.

At first, select the column that contains the date.

Now, from the Home tab, you will find the Number section.

Select the More Number Formats here.

A dialog box will come in front of you. Select Custom from the Category in that dialog box.

Here in the Type field, input four Ms (mmmm), you can use upper or lower Ms. And click OK.

You can see all the dates have been converted into the name of the months.

Now selecting the column, explore the Sort & Filter section from the Data tab.

Select Sort from this section.

Again, since we are selecting a single column from a table the Sort warning dialog box will appear in front of you.

Select your preferred and click OK. We are selecting Expand the selection 

Another dialog box called Sort will pop up.

Click on the drop-down icon of the Order. You will find a Custom List. Click that.

A Custom Lists dialog box will come in front of you. Select the months and click OK. 

Make sure to select the column that contains months. Here Initial Release had the month.

We have found things sorted by months.

4. Built-in SORTBY Function

If you are using Excel 365, then you will find a function called SORTBY. It is a family function of the SORT function.

The SORTBY function sorts the contents of a range or array based on the values from another range or array.

array: Range or array to sort

by_array: Range or array to sort by

sort_order: The order to use for sorting. 1 for ascending, -1 for descending. This is an optional field. Default is ascending.

array/order: Additional array and sort order pairs. This is also an optional field.

SORTBY allows selecting a number of columns as an array. To know more about the function, visit the Microsoft Support site.

Let’s write the formula in Excel.

We have selected the entire column as the array. And our by_array is the Initial Release. But not directly!

We have used the MONTH function to fetch the month number. And the sorting would have been done by that.

Here we have found the dates sorted by month in ascending order. As our default sort_order is ascending, we need not use the field for ascending order.

If you need to sort in descending order, then use -1 instead in the sort_order field like we have done here (image above).

Conclusion

That’s all for today. We have tried listing several ways to sort dates by month. Hope you will this helpful. Feel free to comment if anything seems difficult to understand. Let us know which of the methods you are going to use. Let us know any other approaches which we might have missed here.