AI Excel Generator
← ALL GUIDES

Excel Convert Text to Date Formula: Reliable Methods and Examples

Updated November 4, 2026 · AI Excel Generator

When a date looks right in Excel but sorts or calculates incorrectly, it may be stored as text.

Learn which formula to use for common date formats, how to check the result, and what to do when regional settings get in the way.

How Excel stores dates

Excel stores a date as a serial number and applies a date format to display it.

A genuine date can be added, sorted chronologically, or used in formulas; text that merely looks like a date cannot reliably do those things.

For example, if cell A2 contains the text 3/14/2026, a formula that converts it to an Excel date might return a serial number formatted to display as 3/14/2026.

The underlying value is what matters.

Apply a date format to the result if Excel displays a number instead of a familiar date.

Use DATEVALUE for text Excel recognizes

The simplest formula is =DATEVALUE(A2).

DATEVALUE converts a date stored as text into Excel’s date serial value, provided Excel can interpret the text using the computer’s regional date settings.

For instance, if A2 contains March 14, 2026, =DATEVALUE(A2) returns a date value that you can format as mmm d, yyyy to show Mar 14, 2026.

Copy the formula down the column to convert additional rows.

  • Format the formula result as a date if it appears as a number.
  • If the input includes a time, DATEVALUE may ignore the time portion.

    Use =VALUE(A2) when you need to convert a recognizable date and time together.
  • If DATEVALUE returns #VALUE!, the text may use a different date order, contain extra characters, or be ambiguous under your regional settings.

Build a date with DATE when the text order is known

When a date consistently uses separate year, month, and day parts, the DATE function is often more dependable than asking Excel to guess.

DATE takes arguments in this order: year, month, day.

It returns a genuine Excel date.

Suppose A2 contains an ISO-style text date, 2026-03-14.

If the year, month, and day are always in those fixed positions, use:

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

The formula extracts 2026, 03, and 14, then constructs March 14, 2026.

Format the result as a date.

This positional method works only when the input has that exact structure and uses two-digit month and day fields.

For text in the form 14/03/2026, where the order is day, month, year, use:

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

This creates March 14, 2026, even on a system that ordinarily expects month/day/year.

Adjust the character positions if the text has a different separator or variable-width components.

Convert dates with single-digit month or day values

Fixed-position formulas can fail when one row is 2026-3-4 and another is 2026-11-14.

If the text is consistently separated by hyphens in year-month-day order, split it at the separators instead of relying on character positions.

In Excel for Microsoft 365 or Excel 2024, the formula below uses TEXTSPLIT to separate the components:

=LET(parts,TEXTSPLIT(A2,"-"),DATE(VALUE(INDEX(parts,1,1)),VALUE(INDEX(parts,1,2)),VALUE(INDEX(parts,1,3))))

For 2026-3-4, the result is March 4, 2026.

TEXTSPLIT is available in Microsoft 365 and newer Excel releases that include the function; it is not available in older perpetual versions such as Excel 2019.

In older Excel, use Data > Text to Columns or extract each component with a formula suited to the exact input pattern.

Choose the right method for common date formats

  • Recognizable month name: Try =DATEVALUE(A2) for text such as 14-Mar-2026.
  • Fixed ISO order: Use DATE with text-extraction functions for yyyy-mm-dd.
  • Known day-first order: Construct the date with DATE(year,month,day) so Excel does not mistake the day for a month.
  • Date and time together: Try =VALUE(A2) when Excel recognizes both parts; format the result to display the date, time, or both.
  • Unstructured or mixed data: Standardize the source format first, or use Power Query to split and convert columns with an explicitly selected locale.

Check that the result is a real date

Format the converted cell as General.

A numeric serial value indicates that Excel has stored the result as a number, although the displayed number may be unfamiliar.

You can also test a result in B2 with =ISNUMBER(B2); TRUE indicates that B2 contains a numeric value, as genuine Excel dates do.

Check the date itself before using the converted column.

A formula can produce a valid date while interpreting ambiguous text incorrectly.

For example, 03/04/2026 could mean March 4 or April 3.

Confirm whether the source uses month/day/year or day/month/year, and build the formula around that order.

When you want to replace the original text with converted dates, copy the formula results and use Paste Special > Values.

Keep a copy of the source data until you have checked several rows, including the first and last entries.

Fix common conversion errors

  • #VALUE!: Excel may not recognize the text, or the extraction formula may point to the wrong characters.

    Check separators, spaces, and the assumed date order.
  • Unexpected month and day: The input may be ambiguous under your regional settings.

    Use DATE with explicitly extracted year, month, and day components.
  • A number appears: The conversion may have succeeded, but the cell is formatted as General.

    Apply a date format such as yyyy-mm-dd.
  • The result is off by several years: Review two-digit year conventions and the positions used to extract the year.

    Prefer four-digit years when possible.
  • Some rows convert and others do not: The column may contain inconsistent formats, leading or trailing spaces, or non-date text.

    Inspect the exceptions and handle each format deliberately.

Which Excel versions support these formulas?

DATE, DATEVALUE, VALUE, LEFT, MID, RIGHT, and ISNUMBER are established Excel functions available in older desktop versions as well as current Microsoft 365 releases.

TEXTSPLIT and LET require a newer Excel version, including Microsoft 365 and Excel 2024.

If a workbook must work in an older version, use established text functions or convert the column through Text to Columns.

Once the result is stored as a genuine Excel date, you can sort it chronologically, calculate elapsed days, and use it in functions such as YEAR, MONTH, and DAY.

Choose the formula according to the input pattern, then verify the date order before relying on the converted values.