Sort by Date in Google Sheets When Dates Are dd/mm/yyyy (and Why They Sort Wrong)

If dates written as dd/mm/yyyy sort in the wrong order in Google Sheets, they are almost always stored as text, not as dates. Text is sorted character by character, so 02/03/2026 comes before 15/01/2026 because “0” comes before “1”. The fix is to turn the text into real dates, then sort.

Tested in Google Sheets. On November 9, 2026 we entered the formulas on this page in Google Sheets, with the dates typed as a user would type them in a spreadsheet set to the United States, and compared the results with values calculated independently. How we test

Step 1: Check whether your dates are real dates

In an empty column next to the dates, enter:

=ISNUMBER(A2)

Real dates are numbers in Google Sheets, so this returns TRUE for a real date and FALSE for text. Another clue: by default, real dates are aligned to the right and text to the left.

This usually happens when the data comes from another system or a file with day-first dates, and the spreadsheet’s locale expects month-first dates (for example, the United States). Google Sheets only converts what it recognizes, so 31/12/2026 stays as text because there is no 31st month.

The hidden problem: some dates were converted the wrong way

Day-first dates where the day is 12 or less are worse than text. In a spreadsheet set to the United States, 03/04/2026 (3 April) is accepted as a real date, but as March 4. ISNUMBER returns TRUE, yet the date is wrong. A column can end up with a mix of text and misread dates:

Typed (dd/mm/yyyy) Stored as Meaning in Sheets (US locale)
15/01/2026 Text Not a date
03/04/2026 Date March 4, 2026 (wrong)
31/12/2026 Text Not a date

Found in testing: we typed these three dates into a Google Sheets spreadsheet set to the United States. 15/01/2026 and 31/12/2026 stayed as text, while 03/04/2026 was stored as March 4, 2026 with no warning. The two kinds of problem sat side by side in the same column.

The cleanest fix is to avoid the conversion in the first place (Fix 1). If the data is already in the sheet, use the formula in Fix 2.

Fix 1: Use the right locale before entering or importing the data

Open File → Settings and choose a locale that writes the day first, such as United Kingdom. From then on, dates typed or imported as dd/mm/yyyy are read correctly. Changing the locale does not convert text that is already in the sheet, so paste or import the data again after changing it.

Fix 2: Convert dd/mm/yyyy text with a formula

When the dates are text in dd/mm/yyyy, split them on the slash and rebuild them with DATE, which always takes year, month and day:

=DATE(INDEX(SPLIT(A2, "/"), 3), INDEX(SPLIT(A2, "/"), 2), INDEX(SPLIT(A2, "/"), 1))

This works whether the day and month have one or two digits (5/1/2026 or 05/01/2026). Format the result column as a date with Format → Number → Date.

If the column also contains dates that Google Sheets converted the wrong way (like 03/04/2026 above), swap their day and month at the same time:

=IF(ISNUMBER(A2), DATE(YEAR(A2), DAY(A2), MONTH(A2)), DATE(INDEX(SPLIT(A2, "/"), 3), INDEX(SPLIT(A2, "/"), 2), INDEX(SPLIT(A2, "/"), 1)))

Only use this second version on a column where every value was originally typed as dd/mm/yyyy; otherwise it would swap correct dates too.

Why not DATEVALUE?

DATEVALUE reads a date written as text, but only in the formats your spreadsheet’s locale recognizes. In a United States spreadsheet, =DATEVALUE("31/12/2026") returns #VALUE!, and it cannot fix dates that were already converted the wrong way. The SPLIT formula above works in any locale.

Found in testing: Google’s help page for DATEVALUE says it returns #VALUE! when you pass it a number. In our test, DATEVALUE pointed at a cell that already held a real date returned that date’s serial number instead of an error, so don’t rely on DATEVALUE to tell text dates and real dates apart. Use ISNUMBER for that.

Step 3: Sort by the real dates

Once you have a column of real dates, sort with Data → Sort range, or with a formula that leaves the original data untouched:

=SORT(A2:C, D2:D, TRUE)

Here A2:C is your data and D2:D the converted dates. TRUE sorts from oldest to newest; use FALSE for newest first.

Example

Text in A Converted with Fix 2 (US display) Order when sorted as text Order when sorted by real date
15/01/2026 1/15/2026 02/03/2026 15/01/2026
02/03/2026 3/2/2026 15/01/2026 28/02/2026
28/02/2026 2/28/2026 28/02/2026 02/03/2026

Sorted as text, March comes first because “02” is smaller than “15”. Sorted by the real dates, the order is January, February, March.

Related guides