How to Convert Text to Date in Excel - Practical Guide

Learn how to convert text-formatted dates into true Excel date values for calculations and sorting. This essential skill enables proper date arithmetic, filtering, and prevents errors in data analysis workflows. You'll master multiple conversion methods suitable for different text formats and scenarios.

Why This Matters

Converted dates enable sorting, filtering, and date calculations that text cannot perform, preventing downstream errors in reports and dashboards. Proper date formatting is critical for professional data management.

Prerequisites

•Basic Excel navigation and formula knowledge
•Understanding of date formats (MM/DD/YYYY, DD/MM/YYYY, etc.)
•Familiarity with cell formatting options

Step-by-Step Instructions

Identify Text-Formatted Dates

Select your date column and check if cells are left-aligned (text) rather than right-aligned (numbers). Text dates won't calculate or sort correctly.

Use DATEVALUE Function

In an empty column, enter =DATEVALUE(A1) to convert text dates to serial numbers. Copy the formula down for all rows using Ctrl+C and Ctrl+V.

Copy Converted Values

Select your converted date column, press Ctrl+C to copy, then right-click on the original column and select Paste Special > Values only (Ctrl+Shift+V).

Format as Date

Select the converted cells, right-click > Format Cells (or Ctrl+1), choose Number tab, select Date category, and pick your desired format.

Delete Helper Column

Right-click the helper column header and select Delete to remove the temporary formula column.

Alternative Methods

Text to Columns Method

Select date column, go to Data > Text to Columns, choose Delimited, click Next, select appropriate Date format, and finish. This converts text dates in place without helper columns.

Find & Replace with Wildcards

Use Find & Replace (Ctrl+H) with regular expressions to reformat text dates before conversion, useful for inconsistent date patterns.

Manual Formula Approach

Use DATE(YEAR(), MONTH(), DAY()) combined with VALUE or MID functions for non-standard text date formats requiring custom parsing.

Tips & Tricks

✓Always create a helper column first to preserve original data until conversion is verified.
✓Check regional date settings (File > Options > Advanced > Edit Date) before converting mixed-format dates.
✓Use conditional formatting to identify remaining text dates after conversion for quality control.

Pro Tips

★DATEVALUE works best with standard formats; for unusual formats, combine VALUE() with MID() to extract year/month/day components.
★Use Data > Sort or AutoFilter immediately after conversion to verify dates sorted correctly—if not, conversion failed.
★Apply a date number format (14 or 22 in Format Cells) to see the underlying serial number for troubleshooting.

Troubleshooting

DATEVALUE returns #VALUE! error

The text format doesn't match Excel's expected pattern. Check for leading spaces (use TRIM), inconsistent separators, or non-standard characters. Try DATEVALUE(TRIM(A1)) or inspect the actual cell content.

Dates show as numbers (e.g., 45000) instead of readable dates

Format is still set to Number category. Select cells, press Ctrl+1, go to Number tab, choose Date category, and select a readable date format.

Converted dates are one day off

This indicates a regional date format mismatch (DD/MM vs MM/DD). Verify File > Options > Advanced regional settings match your data, or use DATE(YEAR(), MONTH(), DAY()) formula instead.

Text to Columns doesn't convert dates

Ensure you selected the correct Date format in step 3 of the wizard. If dates still appear as text, the source data may have leading apostrophes—use Find & Replace to remove them.

Related Excel Formulas

DATEVALUE DATE DATEFROMISO8601 TEXT TRIM

Frequently Asked Questions

Why won't my dates sort correctly even after conversion?

If dates still appear left-aligned after conversion, they're still recognized as text. Select the cells, apply Date format from Home > Number Format dropdown or Format Cells dialog (Ctrl+1). Verify the DATEVALUE formula actually returned a number, not text.

Can I convert dates with different formats in one column?

DATEVALUE struggles with mixed formats. Use Text to Columns with careful format selection, or create a custom formula using IF statements to detect and parse each format separately. Standardizing your source data first is the cleanest approach.

What if the text includes time with the date?

DATEVALUE handles dates with times—it converts both to a serial number, keeping the time as a decimal portion. Format the result as Date & Time (Format Cells > Number > Date & Time) to display both components properly.

Is DATEVALUE the only way to convert text dates?

No. Text to Columns, Find & Replace with wildcards, and custom formulas (DATE + MID + VALUE) all work depending on your format. DATEVALUE is fastest for standard formats; use alternatives for unusual patterns or bulk operations.