MrExcel.com - Your One Stop for Excel Tips & Solutions

Convert Text Dates to Real Dates

June 15, 2022 - by

Convert Text Dates to Real Dates

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.

With text 01/21/2014 in C23, a formula of =DATEVALUE(C23) returns 41660. This is the serial number for a date. It just needs to be formatted as a date.
Figure 518. 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.

DATEVALUE returns a #VALUE error if your text is an invalid date, such as February 31 or if Janaauray is spelled wrong.
Figure 519. 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, 2019. 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, 2018”).

Column A contains month abbreviations: Jan, Feb, Mar, and so on to Dec. A formula in C converts it to a date in 2018 with =DATEVALUE(A49&" 1, 2018")
Figure 520. Convert month names to dates.

This article is an excerpt from Power Excel With MrExcel

Title photo by Scott Webb on Unsplash