Meraz Al Nahian
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.
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.
- 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)
- 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.
- 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.
- 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)
- 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, 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.
- 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)
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.
- 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.
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)
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.
- 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.
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
- Auto Sort When Data Is Entered in Excel (3 Methods)
- How to Sort by Last Name in Excel (4 Methods)
- Sorting Columns in Excel While Keeping Rows Together
- How to Sort by Color in Excel (4 Criteria)
- Sort Column by Value in Excel (5 Methods)
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
- Select More Number Formats…
- A dialog box will appear. Select Custom and type “mmmm” in the Type
- 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
- Click Ok.
- 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.
- 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)
- 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.
- 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.
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
- Hit ENTER and you will see the Years of the corresponding dates.
- 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 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.
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
- 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.
- 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.
- 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.
- 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.
- 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.
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
- How to Sort Multiple Columns in Excel (5 Quick Approaches)
- How to Arrange Numbers in Ascending Order in Excel Using Formula
- Excel Sort Dates in Chronological Order (6 Effective Ways)
- Sum Using OFFSET and MATCH in Excel (With Alternative Options)
- How to Sort Two Columns in Excel to Match (Both Exact and Partial Match)
- Random Sort in Excel (Formulas + VBA)
