Excel Convert Text Dates to Real Dates
By Szabó Gergő · Updated
Excel cannot subtract or filter text that only looks like a date. Split the parts and rebuild with DATE.
Syntax and arguments
=DATEVALUE(A2)- text
- A date stored as text, such as 2026-01-31.
Text to date examples
A2 is 2026-01-31.
=DATEVALUE(A2)Works when the file locale understands year-month-day.
A2 is 31/01/2026 and DATEVALUE fails.
=DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2)))Rebuilds day, month, year without asking Excel to guess the locale.
A2 is 31-01-2026.
=LET(p,TEXTSPLIT(A2,"-"),DATE(VALUE(INDEX(p,3)),VALUE(INDEX(p,2)),VALUE(INDEX(p,1))))Splits once, then DATE uses year, month, day in a stable order.
Common mistakes
Adding 0 to text that is not a date serial
A2+0 only works when Excel already stored a date serial as text. Prefer DATEVALUE or DATE.
US vs EU order
Never pass 01/02/2026 to DATEVALUE if you do not know the locale. Split parts instead.
Keeping the display format as text
After conversion, format the cell as a date.
Text to date FAQ
Why does subtraction return #VALUE!?
One side is still text. Convert both to dates first.
Can Power Query do this?
Yes, and it is better for a whole import. Use a formula when one column must stay live.
What about times?
DATEVALUE drops the time. Use VALUE or DATE+TIME for a timestamp.