Shakil Ahmed
One of the most significant features of Excel is the capability to sort data by date. This may be needed for various purposes, e.g. arranging your family members’ birth dates to send out cards, managing employees’ birthdays, sorting out the product delivery or order dates, or sorting your weekly budget activities for the year-end. We can sort dates by day, month, or year. It is particularly important to maintain your daily routine date-wise by sorting the date by month. In this article, we have discussed several techniques of how to sort dates in Excel by month. So, let’s get started.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
4 Methods to Sort Dates in Excel by Month
We have discussed 4 simple methods to sort dates by month. Sometimes, it becomes very useful to sort dates by month for better visualization. We have taken a dataset of some random “Movie” names with their “Initial Release” dates. Now, we will sort the dates by month.
Not to mention, we have used the “Microsoft 365” version. You can use any other version at your convenience.
1. Using Custom Sort Feature
You can use the Custom Sort feature from the Format Cells command in Excel to sort the date by a month. It helps you to extract the month from the date. Follow the steps to do it.
📌 Steps:
- Firstly, select the entire data range of the date and right-click on the cell, and choose Format Cells.
Note: You can use the format cells option by pressing CTRL + 1.
- Apparently, the Format Cells dialog wizard appears. Go to Custom as Category and write “mmmm” in the Type for the full form of the month.
- Lastly, hit OK.
Eventually, your date is sorted by month just like the below image.
Read More: Now and Format Functions in Excel VBA (4 Examples)
2. Utilizing the MONTH Function
You can use the MONTH function to find the number of months from a date. It helps you to understand the month number of a date properly. Go through the steps below.
📌 Steps:
- Firstly, go to cell E5 and write up the formula.
This function checks the entered date of the D5 cell and returns the month number from the date.
Eventually, press ENTER and drag it down for the other cells to get the following result shown below.
Read More: How to Use Excel VBA MONTH Function (7 Suitable Examples)
3. Applying the TEXT Function
The TEXT function mainly takes the date and returns the value by text. You will get the sorted month from the date by this function. Follow the steps to do that.
📌 Steps:
- Initially, go to cell E5 and enter the formula.
This TEXT(D5,”mmmm”) syntax takes the date of the D5 cell as input and the “mmmm” argument makes the full form of the month from the date. Finally, the TEXT function returns the result as text.
- Eventually, press ENTER and drag the Fill Handle tool for the other cells.
Finally, your sorted date by month is presented in the image below.
Read More: How to Use IF Formula with Dates (6 Easy Examples)
4. Employing 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.
📌 Steps:
- Firstly, go to cell F5 and write up the formula.
Here,
D5:D14 = the “Initial Release” date.
B5:D14 = the entire dataset range.
In the above formula, the SORTBY(B5:D14, MONTH(D5:D14)) syntax takes the array from B5:D14, and MONTH(D5:D14) is the by_array1.
Your final result should look like the image below.
Read More: How to Get the Current Date in VBA (3 Ways)
Similar Readings
- How to Use VBA DateSerial Function in Excel (5 Easy Applications)
- Excel Date Picker for Entire Column
- How to Use EoMonth in Excel VBA (5 Examples)
- Excel VBA to Find Week Number (6 Quick Examples)
- How to Use the VBA DatePart Function in Excel (7 Examples)
How to Sort Dates in Excel by Year
Also, we can sort our dates by year. By doing this, we will be able to visualize the full form of the year of the inserted date. Follow the simple steps below.
📌 Steps:
- Firstly, go to cell E5 and enter the formula.
It takes the date of D5 as input and returns the year after sorting by the YEAR function.
Subsequently, you get the result in the image below.
Read More: How to Use Year Function in Excel VBA (5 Suitable Examples)
How to Sort Dates in Excel by Month and Year
Furthermore, we can sort our dates by month and year. We can use the TEXT function to do the operation. Follow the steps to do it.
📌 Steps:
- Firstly, go to cell E5 and write up the formula for sorting month.
This function checks the entered date of the D5 cell and returns the month in text form from the date
Apparently, after pressing ENTER and dragging down the same formula, you will get the result just like the image below.
- Eventually, go to cell F5 and write up the formula. sorting the year.
This function checks the date of the D5 cell entered and returns the year from the date.
How to Sort Dates in Chronological Order in Excel
Chronological means starting of a record of events and following the order as they occurred. In other words, maintaining the numbers or data from smallest to largest means chronology. You may need to sort the dates chronologically. Follow the steps to do it.
📌 Steps:
- Firstly, sort the month as we mentioned earlier in method 2.
- Then, select the entire column of the month, go to the Home tab >> choose Sort & Filter from the Editing group >> pick Sort A to Z.
- Eventually, the Sort Warning dialog box pops out. Check the Expand the selection box and hit Sort.
Consequently, your data sorts chronologically like the image below.
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Conclusion
That’s all about today’s session. And these are some easy methods to sort dates in Excel by month. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out diverse kinds of excel methods. Thanks for your patience in reading this article.
