Excel’s built-in sort options can be a very effective tool, but they do not always work correctly when it comes to sorting dates. This tutorial will teach you how to sort date in excel in chronological order, sort by month ignoring years, sort birthdays and by month and day.  So essentially you’re learning  how to quickly arrange dates in a meaningful way without messing up your data.

Learn How To Sort By Date In Excel

How to sort dates in chronological order

Arranging dates in chronological order in Excel is not a tough task. You just use the standard Ascending Sort option:

1. Select all the dates you want to sort chronologically.

2. On the Home tab, in the Formats group, tap on Sort & Filter and select Sort Oldest to Newest. On the other hand,  you can use the A-Z option on the Data tab, in the Sort & Filter group.
   

How to sort date by date in Excel

The Excel sort options can also be used for re-arranging the whole table, instead of just a single column. To sort records by date keeping the rows intact, the main point here is to expand the selection when prompted.

Here are the steps you need to follow for sorting data in Excel by date wise:

1. In your spreadsheet, select all the dates without the column header.

2. On the Home tab, tap on Sort & Filter and select Sort Oldest to Newest.

3. The Sort Warning dialog box will appear. Leave the default Expand the selection option selected, and select Sort as seen below:

Done, now your records have been sorted by date and all the rows are kept together as you’ll see in the results below. 

How to sort by month in Excel

In certain instances, you might wish to sort dates by month ignoring the year, for example when grouping anniversary dates of your families or church members. In this case, the default Excel sort feature won’t be efficient 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, the example has extracted the month number from the date in B2 using this formula: =MONTH(B2)

Tip. If the result is displayed as date rather than a number, set the General format to the formula cells.

What you need to do here is, sort your table by the Month column. For this, select all the month numbers (C2:C8), tap or click on 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 same results as seen below: 

In this example from Ablebits.com, the data is now sorted by month, ignoring the years and days within each month. Now, in order to sort by month and day, then follow the instructions from the next example. Let’s say 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 or sorting dates for a birthday calendar, the optimal solution will be sorting dates by month and day. In addition to this, you need a formula that would pull out months and days from the dates of birth.

In this case, the Excel TEXT function can convert a date to a text string in the specified format, which becomes super handy. For this 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 biggest to smallest, and you will have the data arranged in order of the days of each month.

The same result can also be produced by using the DATE formula like this:

=DATE(2000, MONTH(B2),DAY(B2))

The formula uses 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, but you can put any. The simple 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, sorting of the dates by month and day, is exactly what you are looking for.

How to sort data by year in Excel

Now focusing on sorting by year, an easy way to arrange dates in chronological order is using Excel’s ascending sort (Oldest to Newest) option.

This method will sort dates by year, then by month, and then by day as shown in the results 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 choosing the DAYfunction, 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 must use 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)

But if your week starts from Monday (1) to Sunday (7), here’s is the right one:

=WEEKDAY(A2, 2)

Here A2 is the cell containing the date.

For this example, we used the first formula and got this result below: 

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)

If you have a list of month names as text, not as dates formatted to display only months, applying Excel’s ascending sort might be an issue in the sense that 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 all 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, click on either full month names (January, February, March, …) or short names (Jan, Feb, Mar…) depending on how the months are listed in your worksheet:
   
5. Tap on 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.

All images of this article are taken from Ablebits.com.