Answer

Text stored as a date is a string that looks like a date but Excel does not recognize it as a valid date value. Excel treats it as text, and this prevents sorting, filtering, and calculations.

How to convert text to date in Excel

To convert text to date in Excel, use built-in tools or formulas depending on the format of the text.

1. Using the Text to Columns tool

This method works when the text follows a consistent format such as 12/25/2024 or 25-12-2024.

1. Select the cells that contain the text.

2. Go to Data > Text to Columns.

3. Select Delimited and click Next.

4. Leave all delimiters unchecked and click Next.

5. Open Column data format and choose Date.

6. Select the date order that matches your text (MDY, DMY, or YMD).

7. Click Finish.

Windows and Mac share identical steps for this tool.

2. Using the DATEVALUE function

The DATEVALUE function converts a recognized text date to a true Excel date. Excel converts the result into a serial number.

1. Use the formula:

   $=DATEVALUE(A1)$
   

2. Press Enter.

3. Format the result as a date by going to Home > Number Format > Short Date or Long Date.

3. Using formulas for irregular formats

Irregular formats require transforming text into a standard date.

a. Converting “YYYYMMDD”

When the text looks like 20241225, split into year, month, and day.

$\text{[}=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))\text{]}$

b. Converting “DD-MM-YYYY” stored as text

Convert separators to a recognizable pattern.

$\text{[}=DATEVALUE(SUBSTITUTE(A1,"-","/"))\text{]}$

4. Using Power Query

Power Query converts large datasets reliably.

1. Select the range.

2. Go to Data > From Table/Range.

3. In Power Query, select the column.

4. Open Data Type and choose Date.

5. Select Close & Load.

Windows and Mac provide the same Power Query interface.

5. Using VALUE for numeric text

The VALUE function converts numeric text that Excel can interpret as dates.

1. Enter:

   $=VALUE(A1)$
   

2. Format the result as a date.

6. Fixing dates that converted incorrectly

When Excel misinterprets the order (e.g., interpreting 05/06/2024 as June instead of May), convert with a formula specifying each part.

$\text{[}=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))\text{]}$

Why text fails to convert

Excel fails to convert text because of one or more of these conditions:

1. The separator is inconsistent (slash, dash, dot, space).

2. The text contains extra spaces.

3. The date uses a format not recognized by the system settings.

4. The entry uses month names misspelled or abbreviated incorrectly.

Trim spaces by using:

$=TRIM(A1)$

How to extract dates from text that includes words

When text includes words such as “Report generated on 12/25/2024”, extract the date with this pattern:

$\text{[}=DATEVALUE(MID(A1,FIND("/",A1)-2,10))\text{]}$

This extracts a 10-character date block like 12/25/2024.

Best practices

1. Standardize date formats before importing data.

2. Use consistent separators in all data sources.

3. Verify converted dates by sorting from oldest to newest.

4. Apply cell formatting only after confirming correct conversion.

If you want, I can show methods tailored to your exact text format.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report