How to Sort Dates in Excel by Month and Year (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

The article will show you some basic methods on how to sort dates in Excel by month and year. It will be pretty simple to understand and apply. In the following figure, we have a dataset on some guys’ birth dates and names.

how to sort dates in excel by month and year


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


1. Applying TEXT Function to Sort Dates by Month and Year

We can also extract Months and Years by using the TEXT function and then Sort them one by one. Let’s see the process below.

Steps:

  • First, make columns for Months and Dates and type the following formula in cell D5.
=TEXT(C5,"mm")

Here, the TEXT function converts the value in cell C5 to Month.

  • Press the ENTER button and you will see the Month of the corresponding Birthdate.

how to sort dates in excel by month and year

  • Use the Fill Handle to AutoFill the lower cells.

  • Now, select Home >> Sort & Filter >> Sort A to Z (We want to sort the months in ascending order, so we chose Sort A to Z)

how to sort dates in excel by month and year

  • A Sort Warning box will appear. Select Expand the selection and click on Sort.

By executing this operation, you can Sort your Dates by Month.

how to sort dates in excel by month and year

  • Now to convert the Dates to Years, type the following formula in cell E5.
=TEXT(C5,"yyyy")

  • Hit ENTER and you will see the Years of the corresponding dates.

how to sort dates in excel by month and year

  • Use the Fill Handle to AutoFill the lower cells.

  • Now, select Home >> Sort & Filter >> Sort A to Z (We want to sort the years in ascending order, so we chose Sort A to Z)

how to sort dates in excel by month and year

  • A Sort Warning box will appear. Select Expand the selection and click on Sort.

By executing this operation, you can sort your Dates by Years.

how to sort dates in excel by month and year

  • You can display Months and Years. To do that, make a new column for months and dates together and type the following formula in cell F5.
=TEXT(C5,"mm/yyyy")

  • Press the ENTER button and you will see the Month and Year stick together in cell F5.

how to sort dates in excel by month and year

  • Use the Fill Handle to Autofill the lower cells.

Thus, you can sort Dates by Month and Year by using the TEXT function.

Read More: How to Sort Excel Sheet by Date


2. Using Excel MONTH and YEAR Functions to Sort Dates by Month and Year

We can convert Dates to Months and Years simply by using the Excel MONTH and YEAR functions and then sorting them one by one. Let’s see the process below.

Steps:

  • First, make columns for Months and Dates and type the following formula in cell D5.
=MONTH(C5)

how to sort dates in excel by month and year

Here, the MONTH function extracts the Month from the Date in cell C5.

  • Press the ENTER button and you will see the Month of the corresponding Birthdate.

  • Use the Fill Handle to AutoFill the lower cells.

how to sort dates in excel by month and year

  • Now, select Home >> Sort & Filter >> Sort Smallest to Largest (We want to sort the months in ascending order, so we chose Sort Smallest to Largest)

  • A Sort Warning box will appear. Select Expand the selection and click on Sort.

how to sort dates in excel by month and year

By executing this operation, you can Sort your Dates by Month.

  • Now to convert the Dates to Years, type the following formula in cell E5.
=YEAR(C5)

how to sort dates in excel by month and year

Here, the YEAR function returns the Year of the corresponding Date of cell C5.

  • Hit ENTER and you will see the Years of the corresponding dates.

  • Use the Fill Handle to AutoFill the lower cells.

how to sort dates in excel by month and year

  • Now, select Home >> Sort & Filter >> Sort Smallest to Largest (We want to sort the years in ascending order, so we chose Sort Smallest to Largest)

  • A Sort Warning box will appear. Select Expand the selection and click on Sort.

how to sort dates in excel by month and year

By executing this operation, you can sort your Dates by Years.

Thus, you can sort Dates by Month and Year by MONTH and YEAR functions.

Read More: How to Sort Birthdays in Excel by Month and Day


3. Implementing Custom Sort Command to Sort Dates by Month and Year

We can convert the Dates to Months and Dates by using Custom Number Formats and then Sort them one by one. Let’s have a look at the process below.

Steps:

  • First, make columns for Months and Dates and select the cells D5:D13.
  • Click on the Number Format

how to sort dates in excel by month and year

  • Select More Number Formats.

  • A dialog box will appear. Select Custom and type “mmmm” in the Type field.
  • Click Ok.

how to sort dates in excel by month and year

  • Do the same for the Year column. Select the cells E5:E13.

  • Open the Number Format Dialog Box.
  • Select Custom and type “yyyy” in the Type field.
  • Click Ok.

how to sort dates in excel by month and year

  • Now type the following formula in cell D5.
=C5

This operation simply  extracts the name of the Months from the Date in cell C5.

  • Press the ENTER button and you will see the Month of the corresponding Birthdate.

how to sort dates in excel by month and year

  • Use the Fill Handle to AutoFill the lower cells.

  • Now, select Home >> Sort & Filter >> Custom Sort (We want to sort the months in ascending order, so we need to choose Custom Sort)

how to sort dates in excel by month and year

  • A Sort Warning box will appear. Select Expand the selection and click on Sort.

  • After that, a dialog box will appear. Select Custom List from this dialog box.

how to sort dates in excel by month and year

  • Then you will see the Custom List Select the months and click Ok.

  • Select Month in the Sort by section and click OK on the Sort Dialog Box.

how to sort dates in excel by month and year

By executing this operation, you can Sort your Dates by Month Names.

  • Now to convert the Dates to Years, type the following formula in cell E5.
=C5

how to sort dates in excel by month and year

  • Hit ENTER and you will see the Years of the corresponding dates.

  • Use the Fill Handle to AutoFill the lower cells.

how to sort dates in excel by month and year

  • Now, select Home >> Sort & Filter >> Sort A to Z (We want to sort the years in ascending order, so we chose Sort Oldest to Newest)

  • A Sort Warning box will appear. Select Expand the selection and click on Sort.

how to sort dates in excel by month and year

By executing this operation, you can sort your Dates by Years.

Thus, you can sort Dates by Month and Year by inserting Custom Sort Command.

Read More: How to Sort by Date in Excel


4. Using Power Query Editor to Sort Dates by Month and Year

Another useful tool to Sort Dates by Month and Year is the Power Query Editor. Let’s go through the process below.

Steps:

  • Select the cells B5:C13 and then go to Data >> From Range/Table

how to sort dates in excel by month and year

  • A dialog box will appear. Just click OK.
  • Make sure that My table has headers

  • Eventually, you will see a new window of Power Query Editor containing the Birthday column. However, we will see a time of 12:00:00 AM by default.

how to sort dates in excel by month and year

  • Now select the header (Birthday) and then go to Add Columns >> Date >> Month >> Month

After that, you will see the month number in a new column.

how to sort dates in excel by month and year

  • Now click on the drop-down icon in the Month header.
  • Select Sort Ascending or Sort Descending whichever you want. In this section, I choose Sort Ascending.

After that, you will see the Months in an Ascending way.

how to sort dates in excel by month and year

  • Select the header (Birthday) again and then go to Add Columns >> Date >> Year >> Year

After that, you will see the Year in a new column.

how to sort dates in excel by month and year

  • Now click on the drop-down icon in the Year header.
  • Select Sort Ascending or Sort Descending whichever you want. In this section, I choose Sort Ascending.

After that, you will see the Years in an Ascending way.

how to sort dates in excel by month and year

Thus you can Sort Dates by Months and Years using the Power Query Editor.

Read More: How to Sort by Month in Excel


Practice Section

Here I have given the dataset that we used to explain these methods so that you can practice these examples on your own.


Download Practice Workbook

You can download the workbook and practice with them.


Conclusion

In a nutshell, I tried to explain the easiest possible ways to Sort Dates by Month and Year in Excel. I hope these interesting methods may benefit you. You can choose any of the methods which suit you the best. If you have other ideas, feedback, or any questions, please feel free to leave them in the comment box.


Related Articles


<< Go Back to Sort by Date in Excel | Sort in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo