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

• Microsoft Excel 97 for Windows

SYMPTOMS

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/97, the date may be interpreted as May 7, 1997 instead of July 5, 1997.
• 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/97 (July 30, 1997), it may be converted into the text string "30/7/97" (without quotation marks).

CAUSE

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

RESOLUTION

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

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel 97. This problem was corrected in Microsoft Excel 97 SR-1.

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

   ARTICLE-ID: Q172475
   TITLE     : OFF97: How to Obtain and Install MS Office 97 SR-1

MORE INFORMATION

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.

   Order            July 5, 1997 represented as
   --------------------------------------------

   month-day-year   7/5/97
   day-month-year   5/7/97
   year-month-day   97/7/5

   English (United States)
   Spanish (Dominican Republic)
   Spanish (Panama)

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.

   Order            Date                    Converted to this order
   ----------------------------------------------------------------

   Day-month-year   5/7/97 (July 5, 1997)   5/7/97 (May 7, 1997)
   Year-month-day   97/7/5 (July 5, 1997)   97/7/5 (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.