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.

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.

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog