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

Method 1 – Applying the TEXT Function to Sort Dates by Month and Year

Steps:

• Make columns for Months and Dates.
• Enter the following formula in cell D5:

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

• Press the ENTER button to see the month and the corresponding birthdate.

• Use the Fill Handle to AutoFill the lower cells.

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

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

• To convert the Dates to Years, enter the following formula in cell E5:

• Press ENTER to see the years and corresponding dates.

• Use the Fill Handle to AutoFill the lower cells.

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

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

• You can display Months and Years. To do that, combine a new column for months and dates and type the following formula in cell F5.

• Press ENTER to see the Month and Year in cell F5.

• Use the Fill Handle to Autofill the lower cells.

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

Steps:

• Make columns for Months and Dates.
• Enter the following formula in cell D5:

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

• Press ENTER to see the month and the corresponding birthdate.

• Use the Fill Handle to AutoFill the lower cells.

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

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

• To convert the Dates to Years, type the following formula in cell E5:

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

• Hit ENTER, and you will see the years on the corresponding dates.

• Use the Fill Handle to AutoFill the lower cells.

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

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

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

Method 3 – Implementing the Custom Sort Command to Sort Dates by Month and Year

Steps:

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

• Select More Number Formats.

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

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

• Enter the following formula in cell D5:

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

• Press the ENTER button to see the month and the corresponding birthdate.

• Use the Fill Handle to AutoFill the lower cells.

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

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

• A dialog box will appear.
• Select Custom List from this dialog box.

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

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

• To convert the Dates to Years, type the following formula in cell E5:

• Press ENTER to see the years and corresponding dates.

• Use the Fill Handle to AutoFill the lower cells.

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

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

Read More: How to Sort by Date in Excel

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

Steps:

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

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

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

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

You will see the month number in a new column.

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

You will see the Months in an Ascending way.

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

You will see the Year in a new column.

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

You will see the Years in an Ascending way.

Read More: How to Sort by Month in Excel

Practice Section

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

Download the Practice Workbook

You can download the workbook and practice.

Related Articles

• How to Sort Dates in Excel by Year
• Excel Sort by Date and Time
• How to Sort Dates in Chronological Order in Excel

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