One of the most significant features of Excel is the capability to sort data by date. This may be needed when we need to arrange your family members’ birth dates to send out cards, manage employees’ birthdays, or sort out the product delivery or order dates. sort your weekly budget activities for the year-end. We can sort dates by day, month, or year. In this article, I will show several techniques to sort dates in Excel by year.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Suitable Ways to Sort Dates by Year in Excel
Let’s consider a dataset of some employees with their ID, Name, Joining Date, and Year. We will use the YEAR, SORTBY functions, the Advanced Filter feature, and the Sort command to sort dates in Excel by year. Here’s an overview of the dataset for today’s task.
1. Combine YEAR Function and Sort & Filter Command to Sort Dates by Year in Excel
Let’s see how we can sort dates by year using the YEAR function and Sort & Filter option. Here, YEAR is the built-in Excel function that returns the year from any given date. Now our target is to sort them according to their joining date years. This way, we can figure out the senior to junior employee list of the company. Let’s follow the instructions below to learn!
- First of all, select cell E5, and write down the YEAR function in that cell. The function will be
- Where D5 is the serial_number of the YEAR function. The YEAR function will return the year of that date.
- Hence, simply press Enter on your keyboard. As a result, you will get the date in year format which is the return of the YEAR function. The return is 2019.
- After that, AutoFill the YEAR function in the rest of the cells in column E.
- Now select the cell range from E5 to E13. Hence, go to the Home tab and select Sort Smallest to Largest from the Sort & Filter drop-down list under the Editing option.
- You can do that by pressing the Right-click on the selected cells as well and selecting the Sort After that, select Sort Smallest to Largest option (For ascending order).
- A Sort Warning dialog box will pop up. Firstly, select Expand the Selection. Secondly, Click on the Sort button.
- Finally, you will be able to sort dates by year.
2. Applying SORTBY Function to Sort Dates by Year Without Mixing Data
There is another popular Excel function named SORTBY. This is used for sorting elements in Excel. Now we will use the same dataset above and sort the dates by year using the SORTBY function.
SORTBY (array, by_array, [sort_order], [array/order], ...)
This is the syntax of the function. Let’s see the arguments details,
array -> This is a required argument and it is for sorting the range or array.
by_array -> This is another required argument and this indicates the range or array to sort by.
sort_order -> This is an optional argument.Only for sorting orders. 1 = ascending (default), -1 = descending.
array/order -> Another optional argument. Additional array and sort sequence pairs.
Let’s follow the instructions below to learn!
- First of all, create a similar heading to the below screenshot. After that, select cell G5 for the convenience of our work.
- Hence, type the SORTBY function in that cell.
Here, B5:E13 is the whole range that is going to be sorted. This range covered the full employee’s information. Then E5:E13 is the range of years, and our sorting will be performed based on this range. Lastly, 1 is used as we are doing ascending sorting here.
- After that, simply press Enter to get the sorted data.
- How to Sort by Date and Time in Excel (4 Useful Methods)
- [Fix] Excel Sort by Date Not Working (2 Causes with Solutions)
- How to Sort Data in Excel Using Formula
- Sort Multiple Columns in Excel (5 Quick Approaches)
- How to Auto Sort Multiple Columns in Excel (2 Useful Methods)
3. Using Advanced Filter Feature to Sort Dates by Year in Multiple Columns
Now let’s see the uses of the Advanced Filter option in Excel for sorting dates by year. For this, we will need a condition. Let’s assume we want all the information of employees who joined between 1-1-2013 and 12-12-2019. Let’s follow the instructions below to learn!
- First of all, from your Data tab, go to,
Data → Sort & Filter → Advanced
- As a result, an Advanced Filter dialog box will appear in front of you. From the Advanced Filter dialog box, firstly, select data range $B$5:$E$13 in the List range drop-down box. Secondly, select data range $C$15:$D$16 in the Criteria range drop-down box. At last, press the OK option.
- Finally, you will get the sorted result accordion to your condition which has been given in the below screenshot.
Interested to learn more about the Advanced Filter?
Visit this Link to learn more about this powerful Excel tool.
Read More: How to Use Advanced Sorting Options in Excel
4. Utilizing Sort Command to Sort Dates by Year in Chronological Order
This is the easiest way to do the sorting. What you need to do is just follow some simple steps.
- First of all, select the table range from B4 to E13. Hence, go to the Data tab then select Sort under the Sort & Filter group.
- As a result, a Sort dialog box will appear in front of you. From the Sort dialog box, firstly, select Joining Date under the Sort by drop-down box. Secondly, select Cell Values under the Sort On drop-down box. Further, you can change the order according to your needs. We select Oldest to Newest under the Order drop-down box. At last, press the OK option.
- Now your all data will be sorted by year.
Notes: Sort by Date Not Working in Excel
While dates are inserted in text format in Excel the Sort by Dates will not work.
These are the ways to sort dates in Excel by year. I have shown all the methods with their respective examples, but there can be many other iterations depending on numerous situations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.