When you have a large data set to analyze, it’s often helpful to sort the dates in chronological order. Of course, Excel has some built-in tools to get the job done, but sometimes they can’t do exactly what we want. In this article, we’ll talk about everything you need to know about sorting dates chronologically.
When you’re through, you’ll be able to sort dates chronologically month-wise, year-wise, and much more. Let’s start by using Excel’s built-in functions and then move on to using a combination of a formula and the built-in functions.
Sort Dates in Chronological Order
When you have a list of dates ready to be sorted in your Excel sheet, all you need to do is – use the Sort Oldest to Newest option.
To do this, select the list of dates you want to sort. Under the Home tab in Excel, locate the Editing group and click on Sort & Filter. From the drop-down, select Sort Oldest to Newest.
This will open a dialog box that will give you two options: Expand the selection or Continue with the current selection.
If you have data in the adjacent columns and you want to rearrange that data as well, select Expand the selection, otherwise choose Continue with the current selection.
In the same way, you can also choose to sort the dates in reverse chronological order by selecting the option Newest to Oldest from the Sort & Filter drop-down.
Sort Dates by Month
Let’s say you’re preparing a list of birthdays so you can throw a pizza party whenever it’s an employee’s birthday. Pizza parties sound great, but it can be tedious to keep track of birthdays each month. Excel can help, though.
In this case, you want the dates sorted by month and the year component is entirely irrelevant. Therefore, the Sort Oldest to Newest option won’t work. Even if you custom format the cells to display just the month component of the date, the date’s serial number won’t change. Therefore, you need an alternative method.
A logical workaround would be to extract a date’s month component in a separate column and then sort that column in chronological order. To do this, we’ll use the MONTH function, like so:
=MONTH(B2)
Once we have the months for each date, we can use the same method we used previously to sort them chronologically. Select the MONTH column and go to the Editing group under the Home tab. Click on Sort & Filter and select Sort Smallest to Largest in the drop-down.
When the dialog box pops, select Expand the selection and this should sort your employees’ birthdays by month.
Note that this method completely ignores the year component while sorting the dates.
Sort Dates by Year
Y’all are smart fellas. You already know how we’re going to do this, don’t you?
Well, we still need to talk about this briefly. If you’re thinking about defaulting to the YEAR function, hold your horses. The method we discussed in the first example also works fine for sorting dates by year. The built-in function (Sort Smallest to Largest option) in Excel will sort dates by year, too.
However, if for some reason you’re keen on using a formula, you can use the YEAR function like so:
=YEAR(B2)
Once your YEAR column is ready, the remaining process is the same. Use the Sort Smallest to Largest option and select Expand this selection when asked by Excel.
This should sort your data by year.
Sort Dates by Month and Day
When you sorted your employees’ birthdays by month, did you notice something odd? Josh and Monica both have their birthday in December. However, in the sorted list, Josh’s birthdate was listed before Monica’s even though Monica’s birthday is five days before Josh’s. It would look terrible if we forget Monica’s birthday and celebrate Josh’s just five days later, wouldn’t it?
This happens because when the output of the MONTH function is sorted, it doesn’t consider the day component of the date. If two rows have the same month, Excel will sort them in order of occurrence. Since Josh and Monica’s MONTH column contains “12” they have been sorted based on their occurrence without considering the day.
We can remedy this by sorting our data based on both, the month and day of the dates. We’ll accomplish this with some logic and the TEXT function, like so:
=TEXT(B2, "mm.dd")
Once you have the text strings in the mm.dd format, use the built-in sort function. This time, you’ll notice that Monica’s birthdate is correctly listed before Josh’s.
Alternatively, we could also use a simple logic where we change the year for all dates to the same year. We can do this using the DATE function. You could use any year, but in this example, let’s use the current year like so:
=DATE(YEAR(TODAY()), MONTH(B2), DAY(B2))
So here, based on the date column we have generated another helper column "Converted Date". Once the column is ready, we can use Excel’s built-in Sort Oldest to Newest option and should have the same output we got using the TEXT function.
Sort Dates by Names of Months or Weekdays
Let’s say instead of the DOBs, you have just the name of the months in which your employees have their birthdays. Now, you want to sort this list chronologically such that employees with their birthday in January are listed first, followed by February, March, and so on.
Excel’s built-in sort option may not be able to arrange this chronologically. If you use it, you’ll see that it sorts the months alphabetically. However, we can use the Custom sort option to accomplish this.
Select the months listed in the Excel sheet and go to Sort & Filter in the Editing group under the Home tab. Select Custom sort and when prompted, select Expand the selection.
Doing this will open a dialog box. In the Sort by menu, select the column containing the months. At the right end, you’ll see a drop-down menu under Order. Expand the menu and select Custom lists.
In the left-pane that contains the custom lists, choose the format in which the months are listed (i.e., Jan, Feb, Mar… or January, February, March…). Press OK twice. You’ll see that the months are now sorted chronologically.
The same method works when you want to sort data by weekdays. If you have weekdays listed instead of the months, the only thing that you need to do differently is to choose the relevant custom list. In the example above, we chose the “January, February, March…” format, but for weekdays you’ll need to choose the “Sunday, Monday, Tuesday…” format.
Excel Sort by Date Not Working
If you seem to have hit a bit of a snag while sorting your dates, this is most likely a formatting issue. Often, the dates in an Excel sheet are not formatted as dates and are stored as text strings. For the above-discussed sorting methods to work, you’ll need to convert the text strings into valid Excel dates.
That wraps up our date sorting saga. Hopefully, these techniques will help you complete sorting exercises fairly quickly. While you practice these methods, we’ll put together another handy Excel tutorial to take you a step closer to becoming an Excel ninja.
