This step-by-step article describes how to convert dates in imported files from yy/mm/dd to mm/dd/yy format in Excel 2000.
Files imported from mainframe computers and other computer systems may contain dates in the format yy/mm/dd. These date formats are not recognized by Excel and are imported as text strings. This article describes how to convert these dates into formats recognized by Excel.back to the top
Method 1: Convert Text to Columns
To convert the data to correctly formatted serialized date numbers, follow these steps:
Select a cell that contains a date that you want to convert, and then click Text to Columns on the Data menu.
NOTE: You can multiple cells if they are in the same column.
In step 1 of the Convert Text to Columns Wizard, click Delimited, and then click Next.
In step 2, select the Tab check box, and then click Next.
In step 3, click Date under Column data format, click the arrow in the box next to Date, click YMD, and then click Finish.
NOTE: Your original data is overwritten unless you enter a new destination. (The default value is the first cell of the range that you selected in step 1.)
For more information about the DATEVALUE worksheet function, click Microsoft Excel Help on the Help menu, type datevalue in the Office Assistant or the Answer Wizard, and then click Search to view the topic.