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 the Practice WorkBook
4 Ways to Sort Dates in Excel by Year
1. Sort Dates in Excel by Year Using Year Function and Sort & Filter Option
Let’s see how we can sort dates by year using the YEAR function and Sort & Filter option. Here YEAR is the build in Excel function which returns the year from any given date. Let’s consider we have a dataset of some employees with their ID, Name, Joining Date, Year. Now our target is to sort them according to their joining date years. This way we can figure out the senior to junior employees list of the company.
Step 1: Enter the formula in E4 and copy down it up to E12
This is another of Excel’s common built-in functions. The syntax of the function is like this:
We need to pass the date as a serial number in the parameter, and this required argument. This function will return the year of the passed date.
[N.B: Problems may occur if dates are inserted in text format. The dates should be in proper date format.]
Step 2: Now go to the Home tab and select Sort & Filter function under the Editing section
Right-click on the selected cells and select the Sort option.
Step 3: Now select Sort Smallest to Largest option (For ascending order)
Step 4: A warning will pop up. Click on the Sort button
Step 5: Now your dates will be sorted by year
Read more: How to Use Year Function in Excel VBA
2. Sort Dates in Excel by Year Using SORTBY Function
There is another popular Excel function name SORTBY. This is used for sorting elements in Excel. Now we will use the same dataset above and sort 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 optional argument.Only for sorting orders. 1 = ascending (default), -1 = descending.
array/order -> Another optional argument. Additional array and sort sequence pairs.
For more details, you can visit this Link
Now our scenario will be like this:
Step 1: Enter the formula in cell B17
Here B4:D12 is the whole range that is going to be sorted. This range covered the full employee’s information. Then E4:E12 is the range of years, and our sorting will be performed based on this range. Lastly, 1 is used as we are doing here ascending sorting.
Step 2: After pressing Enter you get the sorted data
Read more: How to Use Sort Function in Excel VBA
- How to Use Advanced Sorting Options in Excel
- How to Sort Data in Excel Using Formula
- [Fix] Excel Sort by Date Not Working (2 Causes with Solutions)
- How to Sort Multiple Columns in Excel (5 Quick Approaches)
3. Sort Dates in Excel by Year Using Advanced Filter
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 have joined between 1-1-2013 and 12-12-2019
Step 1: Go to the Data tab and select the Advance option under Sort & Filter section
Step 2: Select the List range of the data. Which will be your whole table (including table column headers)
Step 3: Then select the Criteria range then press the OK button
Step 4: Now you will get the sorted result accordion to your condition
Interested to learn more about the Advanced Filter?
Visit this Link to know better about this powerful Excel tool.
Read more: Excel Sort By Date And Time
4. Sort Dates in Excel by Year Using Sort option
This is the easiest way to do the sorting. What you need to do is just follow some simple steps.
Step 1: Select the table
Step 2: Go to the Data tab then select Sort under Sort & Filter section
Step 3: Select Joining Date
Step 4: Other options should be the same. You can change the order according to your need. After that press the OK button
Step 5: Now your all data will be sorted by year
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.