Excel: Convert Text Dates to Real Dates

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I have dates stored as text. How can I convert them to real dates?

Strategy: There are three easy ways to do this.

Method 1 uses the DATEVALUE function. Follow these steps:

  1. Enter a formula such as =DATEVALUE(C23).
  2. Copy the formula down to all of your dates.
  3. Format the results as a Short Date using the Number Format dropdown on the Home tab.
  4. Copy the range containing formulas.
  5. Use Home, Paste dropdown, Paste Values to convert the formulas to values.

  1. Convert the text dates using =DATEVALUE.

    Method 2 uses Paste Special.

    1. Go to any blank cell. Format that cell as a date.
    2. Copy the formatted cell.
    3. Select your range of text dates.
    4. Type Alt+E followed by S, then D, then Enter. This brings up the Paste Special dialog and chooses Add from the operation section. By adding a blank cell to the text, you are forcing Excel to calculate zero + a text date. The result is a real date. The fact that Excel brings along the format of the copied cell is a bonus in this situation.

    Method 3 uses Text to Columns.

    1. Select the range of text dates.
    2. Type Alt+D followed by E then F. This takes you through the default path of the Text to Columns wizard. Excel will convert the text dates to real dates.

    Gotcha: These methods work for 98% of the ways that people enter dates as text. There are some bizarre methods that won't be converted. I once saw a list of events. Something that was scheduled for June 4-6 2014 was entered as 06/4-6/2014. Excel could not convert that date.

  2. DATEVALUE works only if the date is valid.

    Excel fails if the text refers to a date that does not exist, such as February 29, 2015. Since dates in Excel start in 1900, any dates from 1899 and back will not be converted. Also, misspellings cause the date to text conversion to fail.

    If you have cells that contain month names, you can convert those to real dates by concatenating the rest of the date inside the DATEVALUE function. =DATEVALUE(A49&" 1, 2015").

  3. Convert month names to dates.