Converting Mainframe Date Format to Excel

 Last reviewed: November 2, 1994
Article ID: Q43057
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
  • Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

Some mainframes put date information in a "yy,mm,dd" format. Microsoft Excel does not recognize this as a date format when the information is imported. To convert the information into a valid Microsoft Excel date, follow the steps in the section below corresponding to the version of Windows being used.

For Microsoft Excel Running Under Windows 3.0

  1. Run the Windows Control Panel.

  2. Select the International icon.

  3. Choose the Change button in the Date Format section.

  4. Select YMD from the Short Date Format Order section.

  5. Change the List Separator to be a comma (,).

  6. Exit the Control Panel.

  7. Restart Microsoft Excel and open your document.

For Microsoft Excel with Run-Time Windows or Windows 2.x

  1. Run the Control Panel and choose Country Settings from the Preferences menu.

  2. Choose the YMD button and edit the separator to be a comma (,).

  3. Choose OK and exit the Control Panel.

  4. Start Excel and open your worksheet.

  5. Select your data, press F2, and press HOME.

  6. Insert =DATEVALUE(" in front of your data and ") after your data, and press ENTER.

MORE INFORMATION

Leaving the Control Panel preferences set like this facilitates importing dates formatted in this manner.

Return the settings to MDY and "/" if you want, or use the =DATEVALUE function and change the format number to whatever date format you want.

KBCategory: kbusage
KBSubcategory:

Additional words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0
3.00 4.0 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.