How to sort by date in Excel: in chronological order, by month, auto sort

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:

1. Select the dates you want to sort chronologically.
2. 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:

1. In your spreadsheet, select the dates without the column header.
2. On the Home tab, click Sort & Filter and choose Sort Oldest to Newest.
   
3. 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)

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.

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:

1. Select the records that you want to sort by month name.
2. On the Data tab, in the Sort & Filter group, click Sort.
3. 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.

   

4. 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:
   
5. Click OK twice to close both dialog boxes.

Done! Your data has been sorted by month name in chronological order, not alphabetically:

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.

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.

Available downloads

Sort by date formula examples (.xlsx file)
Auto sort macro (.xlsm file)