How to Sort Dates in Excel? 6 Easy Methods
Excel offers a variety of built-in tools to easily sort data in the spreadsheet. However, when it comes to sorting dates, things may get a bit confusing if you are not exactly sure on which basis to sort (weekdays, months or years). Let us say, for example, you want to arrange your co-workers’ birthdays in the order of the months while ignoring the years.
How will you do this in Excel?
In this article, you will learn how to make use of the built-in functions to sort dates in Excel, in any order you wish.
- Where is the Excel Sort Option?
- How to Sort Dates in Excel in Chronological Order?
- How to Sort Dates by Month?
- How to Sort Birthdays by Month and Day?
- How to Sort Dates by Year?
- How to Sort Dates by Month and Year?
- How to Sort by Days of the Week?
Related:
How to Custom Sort Excel Data? 2 Easy Steps
How to Sort a Pivot Table in Excel? 6 Best Methods
How to Filter in Excel? A Step-by-Step Guide
Where is the Excel Sort Option?
You can find the Sort and Filter option in the Home tab of the Excel ribbon.
Or you can find the Sort option under the Data tab also.
Otherwise, you can select any single cell or a group of cells, and right-click on that, you can find the same sort option.
How to Sort Dates in Excel in Chronological Order?
You can sort the dates either in ascending or descending order. First, select the data you want to sort and, then, select the option Sort & Filter. Choose Sort Ascending or Sort Descending.
In Microsoft Excel Office 365 version, the sort options are labelled as Sort Ascending and Sort Descending. The older versions of Excel have these options are called Sort Oldest to Newest and Sort Newest to Oldest.
When you click on Sort Ascending or Sort Descending, a warning dialog box that says “Expand your selection?“ will appear. Choose the Expand and Sort option here.
The rows are sorted based on the year, as shown in the image below.
The sort function re-arranges the whole table, not just a single column. Instead, if you select the DOB column and select Just Sort when you are prompted to, you find that the DOB column is alone sorted and the new arrangement does not correspond to the exact DOB of the person.
If you want to sort by date keeping the rows intact, the key point is to expand the selection when prompted. As you see in the table After Sort, the DOB does not match the person. Select the option Expand and Sort for the corresponding names to appear for the DOB after it is sorted.
Also Read:
Arrow Keys Not Working in Excel – 4 Easy Fixes
Battle of the Excel Lookup Functions: VLOOKUP vs INDEX/MATCH vs XLOOKUP
How to Insert a Page Break in Excel? (3 Simple Steps)
How to Sort Dates by Month?
By default, Excel considers the year to sort the date values. You can sort the dates based on months, in situations where you wish to find the birthdays in a particular month. By default Excel’s MONTH() won’t sort and you need to make use of a helper column and extract the month number and then sort that column.
Make use of the MONTH(), which is a built-in function of Excel.
In the screenshot below, we extract the month number from the date in C2 with this formula:
=MONTH(C2)
Then, to sort your table by the Month column, select the month numbers (C2:C6), click Sort & Filter > Sort Smallest to Largest, and then expand the selection when Excel asks you to do so.
How to Sort Birthdays by Month and Day?
When you make the list for the birthday calendar, you can sort dates by month and day. The formula to pull months and days from the DOB column is =TEXT(C2, “mm.dd”).
The TEXT() function converts a date to a text string in the specified format. For our purpose, the “mmdd” or “mm.dd” format code will work.
Next, sort the Mon & Day column from smallest to largest, and you will have the data arranged in order of the days of each month.
How to Sort Dates by Year?
You can sort the date based on the year ignoring the months and days. To do that, add a helper column Sort by Year with the YEAR() that extracts the year from the date.
Then, you can arrange the table in either ascending or descending order by clicking on the Sort option. You get the table as seen below.
How to Sort Dates by Month and Year?
The MONTH() returns the number associated with it and later you can sort it accordingly. To do that, add a helper column called Month & Year. In the empty cell(D2) next to the first date, type =TEXT(C2, “mm. yy”), where C2 refers to the first date.
This will return a list of months and years, formatted as “mm.yy”. For example, 1/22/1989 will become 01.89. You can use the format, =TEXT(C2, “mm.yyyy”) that will return the year in yyyy format.
How to Sort by Days of the Week?
If you have a table with dates along with the day of the week, you can sort them based on the days. To do this, you will also need a helper column like in the previous examples.
Use the WEEKDAY() formula in the helper column that returns a number corresponding to the day of the week. It is assumed that the week starts from Sunday, hence it returns the number 1, and Saturday would return the number 7.
You can sort the helper column after it is populated with the numbers that represent each weekday.
Suggested Reads:
How to Make a Pareto Chart Excel Dashboard? 4 Easy Steps
How to Make a Sankey Diagram Excel Dashboard? A Step-by-Step Guide
How to Use the NPER Excel Formula: 2 Easy Examples
Closing Thoughts
In this guide, we saw how to sort dates in Excel by dates, month, year, and days of the week. I recommend you test these functions in a practice worksheet to understand them better. Please visit our resources for more high-quality Excel guides.
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel training courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
