EXCEL - How to sort dates in different formats and centuries

Question

I have an EXCEL list that includes a column consisting of dates in different date formats.  My first problem is that using today's date as an example, some of the dates are in the format 2026-03-21 but other dates are in the format 21 Mar 2026.  I want to get all the dates into the same format so that they will sort correctly.  How can I accomplish that?  My second problem is that some of the dates are 20th century (1965, for example) but some are 19th century (1865, for example).  I saw a post once that said that there is a trick to sorting dates from different centuries, but I do not remember the trick.  So, I will appreciate suggestions about how I can get all these dates into the format that will allow me to sort them accurately.

Answer 1

Hi @Robert Judge,

Earlier today I shared with you a solution based on Excel formulas. Now I’m sending you a second version, implemented in Power Query, which performs the same date‑normalization logic but in a more automated and structured way.

Below you will find the full M‑code. It is ready to paste directly into the Advanced Editor of Power Query:

Hope this helps.

IlirU

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ConvertToText = Table.TransformColumnTypes(Source, {{"Date", type text}}),
    SplitInitialParts = Table.SplitColumn(ConvertToText, "Date", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"CoreDate", "Extra1", "Extra2"}),
    KeepCoreDate = Table.SelectColumns(SplitInitialParts, {"CoreDate"}),
    NormalizeSeparatorDash = Table.ReplaceValue(KeepCoreDate, "/", "-", Replacer.ReplaceText, {"CoreDate"}),
    SplitToDMY = Table.SplitColumn(NormalizeSeparatorDash, "CoreDate", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"DayPart", "MonthPart", "YearPart"}),
    DetectMonth = Table.AddColumn(SplitToDMY, "DetectedMonth", each if Number.FromText([MonthPart]?) <= 12 then [MonthPart] else [DayPart]),
    DetectDay = Table.AddColumn(DetectMonth, "DetectedDay", each if Number.FromText([MonthPart]?) > 12 then [MonthPart] else [DayPart]),
    DetermineFinalDay = Table.AddColumn(DetectDay, "DayFinal", each if Text.Length([DetectedDay]) = 4 then [YearPart] else [DetectedDay]),
    DetermineFinalYear = Table.AddColumn(DetermineFinalDay, "YearFinal", each if Text.Length([YearPart]) < 4 then [DayPart] else [YearPart]),
    BuildNormalizedDate = Table.AddColumn(DetermineFinalYear, "NormalizedDate", each Text.PadStart(Text.From([DayFinal]), 2, "0") & "/" & Text.PadStart(Text.From([DetectedMonth]), 2, "0") & "/" & Text.From([YearFinal]), type text),
    FinalOutput = Table.SelectColumns(BuildNormalizedDate, {"NormalizedDate"})
in
    FinalOutput

Answer 2

Hi @Robert Judge,

The date on my computer is in the format dd/mm/yyyy. Below I have built a formula that I think gives the dates in their regular format.

The formula works for Excel 365. I have applied it to cell B2 (see screenshot). Try this formula and tell me if it works for you or not. If it works, then please mark my reply as Valid Answer and kindly upvote it.

IlirU

=LET(
    rd, REDUCE(TEXT(A2:A7, "dd/mm/yyyy"), {".","-"}, LAMBDA(a,b, SUBSTITUTE(a, b, "/"))),
    ts, --TEXTSPLIT(TEXTJOIN(";",, rd), "/", ";"),
  chco, CHOOSECOLS(ts, 1),
  chct, CHOOSECOLS(ts, 2),
  cond, chct <= 12,
        BYROW(HSTACK(TEXT(IF(cond, chco, chct), "00"),
        TEXT(IF(cond, chct, chco), "00"),
        TAKE(ts,, -1)), LAMBDA(a, TEXTJOIN("/",, a)))
)

Answer 3

Hi, your dates are mixed so Excel cannot sort them correctly.

Are the dates stored as text or do they align right in the cells?

Select the column, go to Data > Text to Columns, choose Date and pick a format like YMD to convert text into real dates; if needed use =DATEVALUE(A1) in a helper column. Then apply a single date format from Home > Number Format. Once all values are real dates, Excel sorts correctly across all centuries.

Answer 4

Excel sorts dates correctly only when they are stored as true date values (date serial numbers), not as text. The display format (2026-03-21 vs 21 Mar 2026, or 19th vs 20th century) does not matter once the values are real dates.

Use this approach:

1. Convert all entries to real dates
   • If the dates are already recognized as dates (not text), Excel will sort them correctly regardless of how they look. To check: select a cell and change its format.
   • On the Home tab, in the Number Format box, choose Short Date or Long Date. If the value changes to a proper date (and not #### or stays as text), it is already a real date.
   • If some values do not behave like dates, they are stored as text and must be converted. See Convert dates stored as text to dates for methods to convert them. Once converted, reapply Short Date or Long Date.
2. Normalize the display format (optional but recommended)
   • After everything is a real date, select the entire date column.
   • On the Home tab, in Number Format, choose Short Date or Long Date, or set a custom format such as yyyy-mm-dd or dd mmm yyyy.
   • This only changes how the date looks, not its value; sorting will work across all centuries.
3. Sort the dates (all centuries together)
   • Click any cell in the date column.
   • Go to the Data tab → Sort & Filter group.
   • Choose:
     • Sort Oldest to Newest to get 1800s → 1900s → 2000s, or
     • Sort Newest to Oldest for the reverse.
   • Excel will sort by the underlying date serial number, so 1865, 1965, and 2026 will all fall in correct chronological order.
4. If sorting looks wrong
   • The most common cause is that some entries are still text. In that case:
     • Confirm that all cells in the column are formatted as Date.
     • If some still do not sort correctly, convert those text dates to real dates as in step 1.

Once every entry is a true date, there is no special trick needed for different formats or centuries—Excel’s built-in Sort Oldest to Newest/Newest to Oldest will handle them correctly.

---

References:

• Sort data in a range or table in Excel
• Convert dates stored as text to dates
• Sort a list of data in Excel for Mac
• Sort data using a custom list