Exceljet

Quick, clean, and to the point

Convert text to date

Excel formula: Convert text to date
Generic formula 
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Explanation 

To convert text in an unrecognized date format to a proper Excel date, you can parse the text and assemble a proper date with a formula based on several functions: DATE, LEFT, MID, and RIGHT. In the example shown, the formula in C6 is:

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

This formula extract the year, month, and day values separately, and uses the DATE function to assemble them into the date October 24, 2000.

Note: before you use a formula, see below for other ways to convert text to dates.

Background

When you're working with data from another system, you might run into a situation where dates are not properly recognized by Excel, which instead treats the dates like text. For example, you might have text values like this:

Text Date represented
20001024 October 24, 2000
20050701 July 1, 20115
19980424 April 24, 1998
28.02.2014 February 28, 2014

When Excel has evaluated a date value as text, one option is to use a formula to parse the text into its components (year, month, day) and use these to make a date with the DATE function. As noted above, I recommend you first try the solutions below (adding zero and using text to columns) before you use a formula. Both workarounds are faster and require less effort.

How this formula works

The DATE function creates a valid date using three arguments: year, month, and day:

=DATE(year,month,day)

In cell C6, we use the LEFT, MID, and RIGHT functions to extract each of these components from a text string, and feed the results into the DATE function:

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

The LEFT function extracts the leftmost 4 characters for year, the MID function extracts characters in positions 5-6 for month, and the RIGHT function extracts the rightmost 2 characters as day. Each result is returned directly to the DATE function. The final result is a proper Excel date that can be formatted any way you like.

This approach can be customized as needed.  For example, the unrecognized date format in row 8 is dd.mm.yyyy and the formula in C8 is:

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

Converting text to dates without formulas

Before you use a formula to manually parse and construct a date from text, try one of the fixes below. The first option uses a math operation to "kick" Excel a bit and force it to try and evaluate the text as a number. Because Excel dates are in fact numbers, this can often do the trick. You may need to apply a date format if the operations suceeds.

Add zero to fix dates

Sometimes, you'll encounter dates in a text format that Excel should recognize. In this case, you might be able to force Excel to convert the text values into dates by adding zero to the value. When you add zero, Excel will try to coerce text values to numbers. Since dates are just numbers, this trick is a great way to convert dates in text format that Excel really should understand.

To convert dates in place by adding zero, try Paste Special:

  1. Enter zero (0) in an unused cell and copy to the clipboard
  2. Select the problematic dates
  3. Paste Special > Values > Add
  4. Apply a date format (if needed)

You can also add zero in a formula like this:

=A1+0

where A1 contains an unrecognized date.

Text to columns to fix dates

Another way to get Excel to recognize dates is to use the Text to Columns Feature:

Select the column of dates, then try Data > Text to columns > Fixed > finish

This can sometimes fix everything at once.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.