Data Import & Export

How to Fix Excel for Mac Sorting Dates as Text Instead of Chronologically

Partner Editor Sep 25, 2026 869 views

Question details

The user needs to sort a column of dates chronologically in Excel for Mac, but the application only offers A-Z and Z-A sorting options.

How to Fix Excel for Mac Sorting Dates as Text Instead of Chronologically

Product: Excel for Mac
Device & OS: Mac
Scenario: Sorting a dataset by date where the date entries were imported or typed manually.
Observed behavior: Excel displays alphabetical sorting options (A-Z/Z-A) instead of chronological ones (Oldest to Newest), causing dates to sort in an incorrect, apparently random order because they are recognized as text strings.

Before you start

Verify your Mac's default date delimiter (e.g., slashes or hyphens) in the macOS System Settings under Language & Region, as using unrecognised delimiters like periods can cause dates to be stored as text.

Solution 1Recommended

Convert Text Strings to Valid Date Values using Text to Columns

Since Excel is reading your dates as text, you must convert them to genuine numeric date values before sorting.

A common misconception is that changing the cell format to 'Date' will fix the issue. However, formatting only changes the appearance of valid data. If the underlying data is stored as text, you must force Excel to parse it as a date.

Select the date column

Click and drag to highlight the entire column of dates that are failing to sort correctly.

Open the Text to Columns wizard

Navigate to the 'Data' tab on the Excel ribbon and click on 'Text to Columns'.

Proceed to step 3 of the wizard

Select 'Delimited' and click 'Next' twice until you reach Step 3 of 3 in the wizard.

Specify the date format

Under 'Column data format', select the 'Date' radio button. Choose the format that matches how your text is currently written (for example, choose DMY if your text says 2 Nov 1950), then click 'Finish'.

Sort the corrected data

Keep the column selected, go to the 'Data' tab, and click 'Sort'. You should now see the 'Sort Oldest to Newest' or 'Newest to Oldest' options.

Convert Text Strings to Valid Date Values using Text to Columns

Formatting is Not Enough: Simply changing the cell format via right-click > Format Cells does not convert existing text into date values. The underlying data must be converted first.

Solution 2

Use the DATEVALUE Function to Force Conversion

If the Text to Columns method does not work, you can use a formula in a helper column to extract the real date value.

Smart Date Sorting with WPS

Seamlessly Manage and Sort Dates with WPS Spreadsheet

WPS Office provides a highly compatible and user-friendly spreadsheet tool that easily recognizes date formats, avoiding common text-sorting issues found on Mac. Follow these steps to convert and sort your data flawlessly.

1. Open your file in WPS Spreadsheet: Launch WPS Office for Mac and open the spreadsheet containing your misformatted dates.
2. Highlight the target column: Select the column of dates that are incorrectly acting as text.
3. Use Text to Columns: Navigate to the 'Data' tab and click on the 'Text to Columns' tool.
4. Set the data format: Follow the on-screen prompts, set the Column data format to 'Date', select the appropriate order (e.g., MDY), and click Finish.
5. Sort chronologically: Click 'Sort' from the Data tab to seamlessly arrange your data from Oldest to Newest.

Highly compatible with Microsoft Excel (.xlsx) file formats.Smart date recognition system to prevent text-conversion errors.Available on Mac, Windows, iOS, and Android seamlessly.Free to use with a lightweight installation package.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my sort menu show A-Z instead of Oldest to Newest?

This happens because the spreadsheet software is reading the cell contents as text strings rather than numeric date values. Text is always sorted alphabetically (A-Z or Z-A).

Can I just change the cell format to 'Short Date' to fix the sorting issue?

No. Changing the cell format only changes how the data is visually displayed. If the underlying data was originally entered or imported as text, you must use a tool like Text to Columns or a formula to convert the actual value.

What date formats are recognized by default on Mac?

This depends on your Mac's Language & Region settings in System Preferences. If your system expects a slash (/) as a delimiter (e.g., MM/DD/YYYY) but your spreadsheet data uses periods (e.g., MM.DD.YYYY), the software will fail to recognize it as a date and treat it as text.

Excel & Data Problems