Excel Won’t Sort by Date? Fix It in 5 Steps

Errors & fixes · Updated November 22, 2026

If Excel won’t sort by date, the almost certain cause is that your dates are stored as text, not as real date values. The fix is to convert them using the DATEVALUE function or the Text to Columns wizard, then re-apply your sort. Here’s how to diagnose and solve it in under five minutes.

Why Excel Treats Your Dates as Text

Excel stores dates as serial numbers (e.g., 1/1/2024 is 45292) and only displays them as dates through formatting. When you type a date like “01/01/2024” Excel usually recognizes it, but if the cell contains an apostrophe before the date, a space, or a non-standard format like “2024-01-01” or “Jan 1, 2024,” Excel may store it as text. Text sorts alphabetically, so “10/1/2024” comes before “9/1/2024” because “1” < "9". You can spot text dates because they align left by default, while real dates align right.

Step 1: Check Your Data Type

Select the date column and look at the Number Format dropdown on the Home tab. If it says “Text” or “General” but the cell shows a date, it’s likely text. Also, look for a small green triangle in the corner of the cell – that’s Excel’s error indicator for numbers stored as text. Another quick test: in a blank cell, type =ISNUMBER(A2) (assuming A2 is your first date). If it returns FALSE, your date is text.

Step 2: Convert Text to Real Dates with DATEVALUE

The quickest fix is to add a helper column. Assume your dates are in A2:A100. In B2, enter:

=DATEVALUE(A2)

Drag the formula down. If you get a #VALUE! error, your text isn’t in a recognizable date format. For example, “2024-01-01” won’t work with DATEVALUE in many regional settings. In that case, use a combination like =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)) for YYYY-MM-DD. After converting, copy column B, paste as values over column A, then delete the helper column. Now sort normally.

Step 3: Use Text to Columns for Bulk Conversion

If you have thousands of rows, Text to Columns is faster. Select the date column, go to Data > Text to Columns. Choose Delimited, then Next. Uncheck all delimiters (or keep Tab if your data has tabs), then Next. In Step 3, choose Date and select the format matching your data (e.g., MDY for month-day-year). Click Finish. This forces Excel to parse the text as a date. On Excel for Mac, the path is Data > Text to Columns, same steps.

Step 4: Re-Apply the Sort Correctly

After conversion, select your entire data range (including headers), go to Data > Sort. In the Sort dialog, choose your date column, and under Sort On, select Cell Values. For Order, choose Oldest to Newest or Newest to Oldest. Important: do not select “Sort anything that looks like a number, as a number” – that’s for text numbers. If you still see wrong order, check that your column has no hidden spaces. Use =TRIM(A2) to remove them.

Common Problems and Their Quick Fixes

  • Dates are left-aligned and sort alphabetically: Use the DATEVALUE formula or Text to Columns as above.
  • Sorting only one column instead of the whole table: Always select the entire range before sorting, or use Ctrl+T to make it a Table, then sort via the dropdown arrows.
  • Dates appear as numbers after conversion (e.g., 45292): That’s correct – just apply a date format via Home > Number Format > Short Date.
  • Mixed formats like “01/02/2024” and “2024-01-02”: Standardize them first using a formula like =TEXT(A2,"mm/dd/yyyy") in a helper column, then convert to date.
  • Locale issues: If your system uses DD/MM/YYYY, “01/02/2024” is January 2 in the US but February 1 in the UK. Check your Windows regional settings or use the DATE function explicitly.

How to Prevent This in the Future

Enter dates using the DATE function: =DATE(2024,1,1). Or use the keyboard shortcut Ctrl+; (semicolon) to insert today’s date. When importing data from CSV or other systems, use the Import Data dialog and set the column data format to Date instead of General. Also, avoid using periods or spaces in dates; stick to slashes or dashes. If you receive a file with text dates, run the Text to Columns fix immediately.

Excel vs. Google Sheets: Does It Differ?

Google Sheets has the same issue but a slightly different fix. In Sheets, use =DATEVALUE(A2) as well, but the Text to Columns equivalent is Data > Split text to columns, which doesn’t convert to dates. Instead, use the menu Data > Data cleanup > Trim, then apply Format > Number > Date. Alternatively, use =TO_DATE(A2) if A2 is a number, or =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)) for YYYYMMDD. The sorting works the same once values are true dates.

Frequently Asked Questions

Why does Excel sort dates as text even after I change the format to Date?

Changing the number format only changes how the cell displays, not what’s stored. If the cell contains text, formatting it as a date won’t convert it. You must use the conversion methods above.

My dates are in one column but sorting moves other columns out of sync.

That happens when you sort only the selected column, not the whole range. Select all columns with data, or use Ctrl+T to create a Table, then sort via the header dropdown. The table keeps rows together.

Can I sort by month or weekday instead of full date?

Yes. Add a helper column with =MONTH(A2) or =TEXT(A2,"dddd") to get the weekday name. Then sort by that helper column. For a custom order like Monday to Sunday, use a custom list in Sort > Order > Custom List.

What if my dates have timestamps like “1/1/2024 10:30 AM”?

Those are still real dates if they’re right-aligned. Sorting by date will also sort by time within the same date. If you want to sort by date only, use =INT(A2) to strip the time, then sort.

Why does Excel show ##### instead of the date after sorting?

That’s just the column being too narrow. Select the column, then Home > Format > AutoFit Column Width. It doesn’t affect the sort order.

Is there a way to sort dates without using a helper column?

Yes, if your dates are already real date values. If they’re text, you can use Text to Columns directly on the original column, which converts in place. For a one-time fix, that’s the fastest.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.