Sort by Date in Excel

 

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.

Dates in Excel are right-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.

Simple Sort by Date (Before)

2. To sort in ascending order, on the Data tab, in the Sort & Filter group, click AZ.

Sort in Ascending Order

That's it. Sorting dates in Excel is that easy. More advanced date sorting techniques coming up!

Simple Sort by Date (After)

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.

Sort Dates by Month (Before)

4. To sort in ascending order, on the Data tab, in the Sort & Filter group, click AZ.

Sort in Ascending Order

Done.

Sort Dates by Month (After)

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.

Sort Birthdays (Before)

4. On the Data tab, in the Sort & Filter group, click AZ.

Sort in Ascending Order

VoilĂ . The birthdays are now sorted. Ready to celebrate.

Sort Birthdays (After)

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.

If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Excel Functions and Data Analysis in Excel.