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

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


Download Practice Workbook


4 Ways to Sort Dates in Excel by Month and Year

1. Applying Excel 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

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

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 Birthdays by Month and Day in Excel (5 Ways)


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 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.
=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 by Month in Excel (4 Methods)


Similar Readings


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 in 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
  • 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
  • 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 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 Create Custom Sort in Excel (Both Creating and Using)


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 Dates in Excel by Year (4 Easy Ways)


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.


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

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo