XL97: Problems When Macro Opens/Saves Text Files with Dates

In Microsoft Excel 97, if you run a Visual Basic for Applications macro that opens or saves a text file that contains dates, the following problems may occur:

• The month, day, and/or year of the dates may be reversed. For example, if the text file contains the date 5/7/99, the date may be interpreted as May 7, 1999 instead of July 5, 1999.
• Some dates may be converted into text strings. This problem may occur if the day or year of a date is greater than 12.
  
  For example, if the text file contains the date 30/7/99 (July 30, 1999), it may be converted into the text string 30/7/99.

These problems may occur if you are using regional settings that use a date order of day-month-year or year-month-day.

To correct this problem when opening text files, install Microsoft Excel 97 Service Release 1 (SR-1).

Note that the problem may still occur when you save a text file that contains dates.

Microsoft has confirmed this to be a problem in Microsoft Excel 97. The file-loading problem was corrected in Microsoft Excel 97 SR-1. The file- saving problem still occurs.

For additional information about SR-1, please see the following article in the Microsoft Knowledge Base:

In Microsoft Excel, dates contain three elements: a year, a month, and a day. The order in which these elements are displayed in a date depends on the regional settings in use on your computer. These regional settings vary from country to country. The three main ordering methods used for dates in Microsoft Excel are listed in the following table. The first order, month-day-year, is used in the following regional settings under Microsoft Windows 95 and Microsoft Windows NT: If your computer uses any of these three regional settings, the problems described in the "Symptoms" section in this article should not occur.

However, if you use any other regional settings, you may encounter the problems described in the "Symptoms" section in this article when you run a Visual Basic macro that opens or saves a text file that contains dates. This is because Microsoft Excel 97 may incorrectly use the month-day-year order for dates. As a result, dates may be converted to the month-day-year order. The following table contains an example. Note that if the converted date is not valid in the month-day-year order, the date is converted to a text string.

These problems do not occur if you manually open or save a text file that contains dates. The problem occurs only when you use a macro to open or save such a text file.

This problem does not occur in Microsoft Excel 97 SR-1. This new release of Microsoft Excel 97 works correctly with all regional settings when you use a macro to open or save text files that contain dates.