Microsoft KB Archive/214031

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

Article ID: 214031

Article Last Modified on 8/22/2007

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214031



For a Microsoft Excel 97 and earlier version of this article, see 113616.

IN THIS TASK
SUMMARY
 * Method 1: Convert Text to Columns
 * Method 2: Use a Formula

REFERENCES



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

