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

This article was previously published under Q214031
This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Excel 97 and earlier version of this article, see 113616.


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:
  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.)
back to the top

Method 2: Use a Formula

To convert the date text to a serial number, type the following formula in the worksheet:
B2: =DATEVALUE(MID(A2,4,2)&"/"&RIGHT(A2,2)&"/"&LEFT(A2,2))
This formula assumes that the date to be converted is in cell A2 and that the serial number is in cell B2.

back to the top
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.

back to the top

Article ID: 214031 - Last Review: 12/05/2015 12:26:19 - Revision: 4.2

Microsoft Excel 2000 Standard Edition

  • kbnosurvey kbarchive kbhowto kbhowtomaster KB214031