HOW TO: Convert Dates in Imported Files from yy/mm/dd to mm/dd/yy Format in Excel 2000

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.

Method 1: Convert Text to Columns

To convert the data to correctly formatted serialized date numbers, follow these steps:

1. 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.
2. In step 1 of the Convert Text to Columns Wizard, click Delimited, and then click Next.
3. In step 2, select the Tab check box, and then click Next.
4. 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.)

Method 2: Use a Formula

To convert the date text to a serial number, type the following formula in the worksheet: This formula assumes that the date to be converted is in cell A2 and that the serial number is in cell B2.

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.