Dates are at the heart of reporting. You sort by date to build dashboards, trend charts, aging reports, latest-order views, and anything time based. The headache comes when Excel treats dates as text. That’s when the Sort button only shows A to Z or Z to A instead of Oldest to Newest. The good news is you can fix it in seconds.
Here are common scenarios where you need to sort by date:
- Sales and finance: aging AR/AP, cashflow timelines, month-end reconciliations, or “latest invoice first.”
- Operations and projects: sprint schedules, Gantt timelines, task due dates, ticket triage by newest.
- Marketing and product: cohort start dates, campaign calendars, content editorial queues.
- HR and admin: start dates, contract renewals, certification expirations.
- Data prep: align two datasets by a single date key, group by month for pivot tables, clean exports before loading into a CRM or BI tool.
I’ll walk you through quick fixes for text-dates, reliable conversion for tricky locales, and several ways to sort, including dynamic formulas. I’ll also show when AskExcel can do the cleaning and sorting automatically.
Step 1: Confirm whether your dates are real dates or text
Excel only offers Oldest to Newest when the cells are true dates. Quick checks:
- Real dates are usually right-aligned by default, text left-aligned.
- Change the Number Format to Short Date. If nothing changes, they’re likely text.
- Use
=ISNUMBER(A2). TRUE means Excel sees a real number (dates are stored as serial numbers), FALSE means text.
If you only see A to Z sorting, you almost certainly have text-looking dates.
Step 2: Convert text-looking dates into real dates
Pick the fastest method for your data. Start with these two easiest options.
Option A: The 3-second “replace with itself” trick
A effective nudge is to make Excel re-evaluate the entry:
- Select the date column.
- Press Ctrl+H (Find and Replace).
- Find
/and Replace with/. Click Replace All.
If your dates use-, do-to-.
This forces Excel to parse the values. If your system’s date order matches the text, you’ll immediately see Oldest to Newest appear.
Tip: If you see leading apostrophes like '04/30/2023, remove them with Find ^' Replace with nothing, or use Text to Columns below.
Option B: Text to Columns with a chosen date order
This is the most reliable way when your data uses a different order than your system (for example 30/04/2023 on a US machine).
- Select the column.
- Data > Text to Columns.
- Choose Delimited > Next > Next.
- Under Column data format, pick Date and set the correct order (DMY, MDY, YMD).
Option C: Formula conversion when the text varies
Create a helper column and convert with a formula, then copy and paste values.
- Standard date text like 04/30/2023:
=DATEVALUE(A2) - Date and time text like 04/30/2023 14:35:
=DATEVALUE(A2)+TIMEVALUE(A2) - European-style text on a US system, e.g. 30/04/2023:
=DATE(VALUE(RIGHT(A2,4)), VALUE(MID(A2,4,2)), VALUE(LEFT(A2,2))) - Clean up hidden spaces or nonbreaking spaces first:
=DATEVALUE(SUBSTITUTE(TRIM(SUBSTITUTE(A2,CHAR(160)," ")), ".", "/"))
When the helper shows real dates, copy it and Paste Special > Values over the original column.
Power users: Power Query lets you set a column locale and type to Date, which is ideal for mixed formats at scale.
Step 3: Sort by date
Once Excel recognizes the column as dates, you’ll see:
- Home or Data tab > Sort Oldest to Newest or Newest to Oldest.
- Or open Sort… to sort the whole table by the date column and add secondary keys.
If times are included and you only want calendar-date order, use a helper =INT(A2) to strip the time, then sort on that helper.
Sorting by date with formulas (dynamic ranges)
If you want a formula that returns a sorted range and updates automatically:
- Sort the entire table by the date column C ascending:
=SORTBY(A2:D100, C2:C100, 1) - Sort newest to oldest:
=SORTBY(A2:D100, C2:C100, -1) - Sort by customer A to Z, then by Order Date newest:
=SORTBY(A2:D100, A2:A100, 1, C2:C100, -1)
These are great for dashboards where you never want to click Sort again.
Special cases you’ll probably hit
- Month-only sorting, ignoring year: add a helper
=MONTH(A2)and sort by that. If your dates are text month names, set a custom list (Jan, Feb, …) or convert to real dates first. - Fiscal year labels, FY starting in April:
=YEAR(EDATE(A2,9))gives the fiscal year number; then sort by it. - Blanks: Excel pushes blanks last by default. In formula sorts, you can force blanks to bottom by using a sort key like
=IF(A2="",DATE(9999,12,31),A2). - Wrong order after conversion: that’s almost always a locale mismatch. Use Text to Columns with the correct DMY/MDY choice.
When to use AskExcel to save hours
If your sheet has mixed formats like 13/02/24, Feb 13, 2024, 2024-02-13 07:15, or has hidden apostrophes and nonbreaking spaces, cleaning by hand gets tedious. AskExcel identifies date formats, handles locale differences, and converts everything to real Excel dates, then sorts your entire table in one step.
Try prompts like:
- “Convert column B to real Excel dates. Interpret DMY when day comes first, keep times if present, and sort the whole table by this date newest to oldest.”
- “My dates are a mix of 30/04/2023, April 30, 2023, and 2023-04-30. Normalize them to ISO and re-sort.”
- “Strip times, sort by calendar date only, then group counts by month.”
AskExcel can also generate the exact helper formulas steps for you, then apply them directly so you don’t have to trial-and-error.
A simple recipe you can bookmark
- If you only see A to Z: use Find and Replace to replace
/with/or run Text to Columns with the correct date order. - Verify with
=ISNUMBER()or by switching Number Format to a date. - Sort Oldest to Newest or use
=SORTBY()for a dynamic, always-sorted output. - For messy or mixed formats, let AskExcel normalize and sort in one go.
Once your dates are real, Excel’s sort options just work.
Quick FAQ
Why do I only see A to Z instead of Oldest to Newest?
- Your dates are text. The fastest fix is Ctrl+H and replace
/with/to re-parse, or use Text to Columns with the correct date order.
Does changing the Number Format to Date fix it?
- No. Formatting doesn’t convert text to a date value. You must coerce or parse it.