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

Article translations Article translations
Article ID: 214031 - View products that this article applies to.
This article was previously published under Q214031
Expand all | Collapse all

On This Page

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.

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:
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.

REFERENCES

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.

Properties

Article ID: 214031 - Last Review: August 22, 2007 - Revision: 4.2
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
Keywords: 
kbhowto kbhowtomaster KB214031

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com