Date & Time
Convert DD/MM/YYYY text strings to real spreadsheet dates without locale bugs
Parse slash-separated DD/MM/YYYY text of variable digit width using DATE, TEXTBEFORE, and TEXTAFTER so days and months never swap.
Why DATEVALUE and fixed MID positions fail on imported DD/MM/YYYY text
On a US-locale workbook, DATEVALUE("25/04/2026") returns #VALUE! because there is no 25th month, and worse, DATEVALUE("7/9/2026") silently misinterprets September 7 as July 9.
Fixed MID(A2, 4, 2) slicing also breaks whenever single-digit days or months omit leading zeros ("7/9/2026"). Delimiter-based TEXTBEFORE and TEXTAFTER extract Year, Month, and Day accurately regardless of 1- or 2-digit width.
The TEXT-wrapped result is display text; use the DATE-only formula below for a numeric date. Validate day/month/year before conversion: DATE normalizes impossible dates such as 31/04 into May rather than rejecting them.
| RawDateText (A) | Extracted Day | Extracted Month | ISO Date Result |
|---|---|---|---|
| 25/04/2026 | 25 | 4 | 2026-04-25 |
| 7/9/2026 | 7 | 9 | 2026-09-07 |
Delimiter-safe DD/MM/YYYY parser
TEXTAFTER(A2, "/", 2) grabs the year after the second slash, TEXTBEFORE(TEXTAFTER(A2, "/"), "/") isolates the middle month token, and TEXTBEFORE(A2, "/") grabs the leading day token.
=TEXT(DATE(VALUE(TEXTAFTER(A2, "/", 2)), VALUE(TEXTBEFORE(TEXTAFTER(A2, "/"), "/")), VALUE(TEXTBEFORE(A2, "/"))), "yyyy-mm-dd")Verify real numeric date serial output
DATE(2026, 4, 25) produces real serial number 46137, ready for EOMONTH, DATEDIF, or sorting.
=DATE(VALUE(TEXTAFTER(A2, "/", 2)), VALUE(TEXTBEFORE(TEXTAFTER(A2, "/"), "/")), VALUE(TEXTBEFORE(A2, "/")))Try the example
Use the original values or change them to test your understanding.