Collecting and then organizing data in Excel is one of the most repetitive tasks you may need to do. To sort data, users tend to get benefits in multiple ways. Sometimes, you might be needing to sort data by date. Your data may contain birth dates, weekly budget plans, or sales revenue by month. To handle this kind of data, you have to understand how to sort by date in Excel.
You can use different ways to sort by date. Moreover, you can even put data in ascending or descending order. Besides, you can sort by day, week, month, or year. Today, you will get to know how you can sort data by date in Excel.
How to Sort by Date on Excel?
You can re-arrange an entire table with Excel sort options. Plus, you can manage records by date as well to keep the rows together. Below you will find out steps that cover how you can handle data in Excel by date.
- From the sheet, choose the dates except for the column header.
- Open the Home tab and choose Sort & Filter option.
- Now, click on the Sort Oldest to Newest option.
- You will see a Sort Warning dialog box. Click the Sort option and leave the Expand the selection option checked.
All done!
You can see all the records are sorted by date while keeping the rows together.
How to Sort by Date on Excel in Chronological Order?
Choose ascending or descending order to sort the dates. For this, you may follow the steps given below:
- First, choose the data you need to sort.
- Click on the Sort & Filter option.
- Choose the Sort Ascending or Sort Descending option.
- Choose a column with the dates.
- Now, click on the Sort Ascending option from the Sort & Filter menu.
- You will see a warning dialog box that appears with “Expand your selection?” when you click on Sort Ascending or Sort Descending option.
- From there, choose the Expand and Sort options.
How to Sort Dates by Month and Year?
The MONTH() returns the number linked with it and then you can easily sort it. For this, you may need to add a helper column called Month & Year.
Enter =TEXT(C2, “mm. yy”) in the blank cell D2 right next to the first date.
You will see a list of months and years, formatted as “mm. yy”.
Suppose, you need to add 1/22/1989, it will be as 01.89. For this, follow this format:
=TEXT(C2, “mm.yyy”)
It will return the year in yyy format.
With the help of the TEXT function, you can drag out the Months and Years data while sorting them in ascending order.
How to Auto-Sort by Date in Excel?
The Excel sort feature can handle many issues but it holds a drawback and that is it is not dynamic. It simply shows that the data needs to be re-sorted whenever you update it with new information. Therefore, users tend to find ways to auto-sorting data in Excel every time a new date or information is added to the data.
Below you will find code examples of macros that let you sort data by date in chronological order.
Macro Code 1: Auto Sort with Every Worksheet Change
You can use this macro when you see that something has changed in the sheet.
Suppose that your data is in Columns A to C and the dates you need to sort are present in column C. Apart from that row 1 has headers (Header:=x1Yes).
Below is the adjustment when your data is in different columns.
Modify the A1 reference to the top left cell of the destined range.
Modify the C2 reference to the uppermost cell with a date.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Range(“A1”).Sort Key1:=Range(“C2”), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End Sub
The Best Ways to Sort by Date on Excel:
Sorting data is not an interesting task, especially when you need to sort by dates. You need to do it properly so that you don’t have to face issues later. When you sort data by dates correctly, it makes your sheet super easy to understand.
