Table of Contents
Sort by Date
This guide walks you through the steps to sort by date in Excel.
It starts with how to convert text-formatted dates to actual date values followed by basic sorting methods and advanced techniques like sorting dates by month and sorting birthdays.
Convert Text Dates to Real Dates
Excel sometimes fails to recognize dates because they are formatted as text which prevents proper sorting. Properly formatted dates are right-aligned whereas text values are left-aligned.
Use the methods below until your dates are right-aligned. If your dates are right-aligned you’re ready to sort by date in Excel.
Method 1: Apply a Date Format
1. Select the text dates.
2. On the Home tab in the Number group select “Short Date” from the Number Format drop-down list.
Method 2: Use Find and Replace
1. Select the text dates.
2. Press Ctrl + H to open the Find and Replace dialog box.
3. In the Find what box type a single slash (/) and in the Replace with box type a single slash (/) again then click Replace All.
Method 3: Use Text to Columns
1. Select the text dates.
2. On the Data tab in the Data Tools group click Text to Columns.
3. In the wizard select Delimited and click Next.
4. Ensure no delimiter boxes are checked and click Next.
5. Choose Date under Column data format select the format that corresponds to your data (e.g. MDY for month-day-year) and click Finish.
Simple Sort by Date
Sorting starts once your dates are recognized correctly by Excel. To sort dates in chronological order execute the following steps.
1. Select a date in the Date of Birth column.
2. To sort in ascending order on the Data tab in the Sort & Filter group click AZ.
That’s it. Sorting dates in Excel is that easy. More advanced date sorting techniques coming up!
Note: to sort dates from newest to oldest on the Data tab in the Sort & Filter group click ZA.
Sort Dates by Month
For more detailed sorting like sorting dates by month follow these steps.
1. Use the formula =MONTH(B2) in a new column next to the dates and drag the formula down to fill the column.
2. Label this new column as “Month.”
3. Select a cell in the Month column.
4. To sort in ascending order on the Data tab in the Sort & Filter group click AZ.
Done.
Conclusion: after completing the date sorting by month remember that the focus is solely on organizing by months with no consideration for the years.
Sort Birthdays
To sort birthdays by month and day use the TEXT function in Excel which formats the dates for sorting without the year. This technique organizes birthdays sequentially.
1. Add a new column called “Birthday Key” next to Date of Birth.
2. Enter the formula =TEXT(B2 “mm.dd”) in the first cell of this column and drag it down to apply to all dates.
3. Select a cell in the Birthday Key column.
4. On the Data tab in the Sort & Filter group click AZ.
Voilà. The birthdays are now sorted. Ready to celebrate.
Conclusion: this method arranges birthdays in a way that is easy to view and manage similar to looking at a calendar focusing on the day and month for each entry.
