Microsoft Office Tutorials and References
In Depth Information
Converting Text Dates to Dates
There are other methods for converting the data shown in Figure 3.11 to dates. Here are two
Method 1: Select any empty cell. Press Ctrl+C to copy. Select your dates. On the Home tab, select Paste,
Paste Special. In the Paste Special dialog, choose Values in the Paste section and Add in the Operation
section. Click OK. The text dates will convert to dates.
Method 2: Select the text dates and then, on the Data tab, select Text to Columns, and then click Finish.
Converting Bizarre Text Dates to Real Dates
When you rely on others for source data, you are likely to encounter dates in all sorts of
bizarre formats. For example, while gathering data for this topic, I found a dataset where
each date was listed as a range of dates. Each date was in the format 2/4-6/11. I had to
check with the author of the data to find out if they meant February 4th through 6th of
2011 or if they meant February 4th through June 11th. They meant the former.
Used in combination, the functions listed below can be useful when you are converting
strange text dates to real dates:
=DATE(2011,12,31) —Returns the serial number for December 31, 2011.
=LEFT(A1,2) —Returns the two leftmost characters from cell A1.
=RIGHT(A1,2) —Returns the two rightmost characters from cell A1.
=MID(A1,3,2) —Returns the third and fourth characters from cell A2. You read the
function as “return the middle characters from A1, starting at character position 3, for
a length of 2.”
=FIND(“/”,A1) —Finds the position number of the first slash within A1.
Follow these steps to convert the text date ranges shown in Figure 3.14 to real dates:
A mix of LEFT , RIGHT ,
MID , and FIND
functions parse this text to
be used in the DATE