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.
Arranging dates in chronological order in Excel is very easy. You just use the standard Ascending Sort option:
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:
That's it! The records have been sorted by date and all the rows are kept together:
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:
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.
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:
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:
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.
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:
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:
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:
If your week starts from Monday (1) to Sunday (7), here's is the right one:
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.
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:
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.
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.
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:
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.
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.
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:
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.
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!
Table of contents