Convert Text to a Real Date

All versionsDATEVALUEDATE

Dates imported as text won’t sort, filter, or calculate. DATEVALUE converts a text date into a real one; for stubborn formats (like 20260617), rebuild it with DATE and the text functions.

Quick formula: to convert a text date in A2 to a real date:

=DATEVALUE(A2)

DATEVALUE reads a recognizable date string and returns the serial number; format the cell as a date to see it normally.

Functions used (tap for the full reference guide):

DATEVALUE DATE LEFT

The example

Text dates (left-aligned) converted to real dates.

	A	B
1	Text date	Real date
2	2026-06-17	6/17/2026
3	17/06/2026	6/17/2026
4	20260617	6/17/2026

The formula

The basic conversion:

=DATEVALUE(A2) // "2026-06-17" (text) → a real date

How it works

DATEVALUE parses recognizable formats; DATE rebuilds the rest:

DATEVALUE(A2) turns a text date Excel recognizes (like "2026-06-17") into a date serial number. Format the cell as a date to display it.
For a packed format like 20260617 that DATEVALUE can’t read, slice it apart and rebuild: =DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)).
A quick no-formula fix for a whole column: select it, Data → Text to Columns → Finish — Excel often converts text dates in place.
Tell text dates apart from real ones by alignment: text left-aligns, real dates right-align.

Fastest bulk fix: select the column, then Data → Text to Columns → Finish — or multiply by 1 (=A2*1) and format as a date. Both coerce recognizable text dates to real ones.

Try it: interactive demo

Live demo

Type a text date; see whether it parses to a real date.

Text date (A2)

Parsed:

Variations

Packed format (yyyymmdd)

Slice and rebuild with DATE:

=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2))

Coerce with math

Multiply by 1 (then format as date):

=A2 * 1

Date and time text

Split with DATEVALUE + TIMEVALUE:

=DATEVALUE(LEFT(A2,10)) + TIMEVALUE(MID(A2,12,8))

Pitfalls & errors

#VALUE! from DATEVALUE. The text isn’t a format Excel recognizes (often a day/month order that conflicts with your locale). Rebuild with DATE, or fix the order.

Locale matters. "17/06/2026" fails in a US locale (no month 17). Use DATE with explicit parts to avoid ambiguity.

Result looks like a number. A converted date shows as a serial (e.g. 46190) until you apply a Date format.

Practice workbook

📊

Download the free Convert Text to a Real Date practice workbook

Text dates in several formats with live DATEVALUE and DATE-rebuild conversions, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I convert text to a date in Excel?

Use =DATEVALUE(A2) for recognizable date strings, then format the cell as a date. For packed formats like 20260617, rebuild with =DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)).

Why does DATEVALUE return #VALUE!?

The text isn't a date format your locale recognizes, often because the day/month order conflicts. Rebuild the date with DATE and explicit parts, or fix the order.

How do I convert a whole column of text dates at once?

Select the column and use Data > Text to Columns > Finish, or multiply by 1 (=A2*1) and apply a date format. Both coerce recognizable text dates to real ones.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Days until a date · Add months to a date · Check if number or text

Function references: DATEVALUE · DATE · LEFT