USER PROBLEM: I have dates stored as text. How can I convert them into real dates?

SOLUTION: 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.

Issue-147--November-10fig13). 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 (Figure 1, at left).

The second method 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.

Issue-147--November-10fig24). 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 the Text to Columns process.

1). Select the range of text dates.

2). Type Alt+D followed by E then F. This goes to the default path of the Text to Columns wizard. Excel converts the text dates to real dates.

Issue-147--November-10fig3CAUTION: These methods work for 98% of the ways that people enter dates as text. There are some bizarre methods that won’t be converted. Once, on a list of events, something scheduled for June 4-6 2014 was entered as 06/4-6/2014. Excel could not convert that date (Figure 2, above).

Excel cannot convert dates that do not exist, such as February 29, 2015. Dates in Excel start in 1900, so dates from 1899 and back are not converted. Also, misspellings fail to convert.

Cells with month names can be converted to real dates. Concatenate the rest of the date inside the DATEVALUE function. =DATE VALUE (A49&” 1, 2015”) (Figure 3, at left).

This article first appeared in the November 10, 2014, edition of the CFO LearningPro, Excel Edition, newsletter. Click here to see a video explaining the solution.

CFO contributor Bill Jelen. a.k.a. Mr. Excel, is an Excel MVP and the author of 35 books about Microsoft Excel. 

, , , , , , ,

2 responses to “Excel Tip: Time Formats”

Leave a Reply

Your email address will not be published. Required fields are marked *