In this article, we will look at different ways to sort dates in Excel. You will learn how to quickly arrange dates in chronological order, sort by month ignoring years, sort birthdays by month and day, and how to auto sort by date when entering new values.
Excel's built-in sort options are powerful and effective tools, but they do not always work correctly when it comes to sorting dates. This tutorial will teach you a few useful tricks to arrange Excel by date in a meaningful way without messing up your data.
How to sort dates in chronological order
Arranging dates in chronological order in Excel is very easy. You just use the standard Ascending Sort option:
- Select the dates you want to sort chronologically.
- On the Home tab, in the Formats group, click Sort & Filter and select Sort Oldest to Newest. Alternatively, you can use the A-Z option on the Data tab, in the Sort & Filter group.
How to sort by date in Excel
The Excel sort options can also be used for re-arranging the whole table, not just a single column. To sort records by date keeping the rows intact, the key point is to expand the selection when prompted.
Here are the detailed steps sort data in Excel by date wise:
- In your spreadsheet, select the dates without the column header.
- On the Home tab, click Sort & Filter and choose Sort Oldest to Newest.
- The Sort Warning dialog box will appear. Leave the default Expand the selection option selected, and click Sort:
That's it! The records have been sorted by date and all the rows are kept together:
How to sort by month in Excel
There may be times when you wish to sort dates by month ignoring the year, for example when grouping anniversary dates of your colleagues or relatives. In this case, the default Excel sort feature won't work because it always considers the year, even if your cells are formatted to display only the month or month and day.
The solution is to add a helper column, extract the month number and sort by that column. To get a month from date, use the MONTH function.
In the screenshot below, we extract the month number from the date in B2 with this formula:
=MONTH(B2)
Tip. If the result is displayed as date rather than a number, set the General format to the formula cells.
And now, sort your table by the Month column. For this, select the month numbers (C2:C8), click Sort & Filter > Sort Smallest to Largest, and then expand the selection when Excel asks you to do so. If all done correctly, you will get the following result:
Please pay attention that our data is now sorted by month, ignoring the years and days within each month. If you'd like to sort by month and day, then follow the instructions from the next example.
If the month names are entered as text, then sort by a custom list as explained in this example.
How to sort birthdays in Excel by month and day
When arranging dates for a birthday calendar, the optimal solution will be sorting dates by month and day. Consequently, you need a formula that would pull months and days from the dates of birth.
In this case, the Excel TEXT function, which can convert a date to a text string in the specified format, comes in handy. For our purpose, the "mmdd" or "mm.dd" format code will work.
With the source date in B2, the formula takes this form:
=TEXT(B2, "mm.dd")
Next, sort the Month and day column from largest to smallest, and you will have the data arranged in order of the days of each month.
The same result can be achieved by using the DATE formula like this:
=DATE(2000, MONTH(B2),DAY(B2))
The formula generates a list of dates by extracting the month and day from the actual date in B2 and replacing the real year with a fake one, 2000 in this example, though you can put any. The idea is to have the same year for all the dates, and then sort the list of dates in chronological order. Since the year is the same, the dates will be sorted by month and day, which is exactly what you are looking for.
How to sort data by year in Excel
When it comes to sorting by year, the easiest way is to arrange dates in chronological order with Excel's ascending sort (Oldest to Newest) option.
This will sort dates by year, then by month, and then by day as shown in the screenshot below.
If for some reason you are not happy with such an arrangement, then you can add a helper column with the YEAR formula that extracts the year from the date:
=YEAR(C2)
After sorting data by the Year column, you will notice that the dates are sorted by year only, ignoring months and days.
Tip. If you'd like to sort dates by day disregarding months and years, extract the day by using the DAY function, and then sort by the Day column:
=DAY(B2)
How to sort by days of the week in Excel
To sort data by weekday, you will also need a helper column like in the previous examples. In this case, we will be populating the helper column with the WEEKDAY formula that returns a number corresponding to the day of the week, and then sorting by the helper column.
For a week that starts from Sunday (1) to Saturday (7), this is the formula to use:
=WEEKDAY(A2)
If your week starts from Monday (1) to Sunday (7), here's is the right one:
=WEEKDAY(A2, 2)
Where A2 is the cell containing the date.
For this example, we used the first formula and got this result:
In case the weekday names are entered as text, not as dates, then use the Custom Sort feature as explained in the next example.
How to sort data in Excel by month names (or weekday names)
In case you have a list of month names as text, not as dates formatted to display only months, applying Excel's ascending sort might be a problem - it will arrange the months names alphabetically instead of sorting by month order from January to December. In this case, a custom sort will help:
- Select the records that you want to sort by month name.
- On the Data tab, in the Sort & Filter group, click Sort.
- In the Sort dialog box, do the following:
- Under Column, select the name of the column that contains the month names.
- Under Sort on, choose Cell Values.
- Under Order, select Custom List.
- In the Custom Lists dialog box, select either full month names (January, February, March, …) or short names (Jan, Feb, Mar…) depending on how the months are listed in your worksheet:
- Click OK twice to close both dialog boxes.
Done! Your data has been sorted by month name in chronological order, not alphabetically:
Tip. To sort by the names of the days of the week, select either full names (Sunday, Monday, Tuesday, …) or short names (Sun, Mon, Tue…) in the Custom Lists dialog box.
How to auto sort by date in Excel
As you have seen, the Excel Sort feature copes with a variety of challenges. The only drawback is that it isn't dynamic. Meaning, you will have to re-sort your data with every change and whenever new information is added. Perhaps you are wondering if there is a way to sort automatically every time a new date is added so that your data is always in order.
The best way to accomplish this is by using a macro. Below, you will find a couple of code examples to auto sort the following data by date in chronological order.
Macro 1: Auto sort with every worksheet change
This macro is executed whenever a change occurs anywhere in the worksheet.
It is assumed that your data are in columns A through C, and the dates that you want to sort by are in column C, beginning in C2. It is also assumed that row 1 contains headers (Header:=xlYes). If your records are in different columns, then make the following adjustments:
- Change the A1 reference to the upper left cell of your target range (including headers).
- Change the C2 reference to the topmost cell containing 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:=xlTopToBottomEnd Sub
Macro 2: Auto sort when changes are made to a specific range
If you are working with a huge worksheet that contains a lot of information, re-sorting with absolutely any change in the sheet may be troublesome. In this case, it makes sense to limit the triggering of the macro to the changes that occur in a specific range. The following VBA code sorts the data only when a change is made in column C that contains the dates.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C:C")) Is Nothing Then Range("A1").Sort Key1:=Range("C2"), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End IfEnd Sub
Tip. These macros can be used to auto sort by any data type, not just dates. Our sample codes sort in ascending order. If you'd like to sort descending, change Order1:=xlAscending to Order1:=xlDescending.
How to add the macro to your worksheet
As both macros are run automatically on a worksheet's change, the code should be inserted in the sheet where you want to sort data (Sheet1 in this example). Here's how:
- Press Alt + F11 to open the VBA Editor.
- In the Project Explorer on the left, double click the sheet where you want to auto sort.
- Paste the code in the Code window.
Auto sort dates with formula
Supposing you have a list of dates and you'd like to have them automatically arranged in chronological order in a separate column, side-by-side with the original list. This can be done with the following array formula:
=IFERROR(INDEX($A$2:$A$20, MATCH(ROWS($A$2:A2), COUNTIF($A$2:$A$20, "<="&$A$2:$A$20), 0)), "")
Where A2:A20 are the original (unsorted) dates, including a few empty cells for possible new entries.
Enter the formula in a blank cell beside the column with the original dates (C2 in this example) and press the Ctrl + Shift + Enter keys simultaneously to complete it. Then, drag the formula down to the remaining cells (C2:C20 in our case).
Tip. To have newly added dates sorted automatically, be sure to include a sufficient number of blank cells in the referred range. For instance, our list of dates is in the range A2:A7, but we supply $A$2:$A$20 to the formula, and populate it into the cells C2 through C20. The IFERROR function prevents errors in the extra cells, returning an empty string ("") instead.
Excel sort by date not working
If your dates are not sorted as they should, most likely they are entered in a format that Excel cannot understand, therefore they are perceived as text strings rather than dates. The following tutorial explains how to distinguish the so-called "text dates" and convert them to normal Excel dates: How to convert text to date in Excel.
That's how to sort by date in Excel. I thank you for reading and hope to see you on our blog next week!
Available downloads
Sort by date formula examples (.xlsx file)
Auto sort macro (.xlsm file)
You may also be interested in
- How to sort columns and rows alphabetically
- How to sort in Excel by row, column names and in custom order
- How to filter and sort by cell color in Excel
- Sort mixed numbers and text / hierarchy numbers in Excel
- Excel Date functions
- Calculate days since or until a date
