Microsoft KB Archive/43057

{| = Converting Mainframe Date Format to Excel =
 * width="100%"|

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 &quot;yy,mm,dd&quot; 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(&quot; in front of your data and &quot;) 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 &quot;/&quot; if you want, or use the =DATEVALUE function and change the format number to whatever date format you want.
 * }