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

ID: Q171278

The information in this article applies to:

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

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

STATUS

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:

Q172475 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, 1999 represented as
   --------------------------------------------

   month-day-year   7/5/99
   day-month-year   5/7/99
   year-month-day   99/7/5

The first order, month-day-year, is used in the following regional settings under Microsoft Windows 95 and Microsoft Windows NT:

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

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.


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

   Day-month-year   5/7/99 (July 5, 1999)   5/7/99 (May 7, 1999)
   Year-month-day   99/7/5 (July 5, 1999)   99/7/5 (text string)

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.

Additional query words: XL97 australian canadian new zealand french german italian british norwegian portuguese swedish danish ireland south africa argentina chile colombia paraguay uruguay peru mexican costa rica ecuador guatemala venezuela brazilian swiss icelandic indonesian afrikaans basque catalan dutch belgian finnish luxembourg austrian liechtenstein caribbean jamaica nynorsk bokmal sr1 y2k year2000

Keywords : kbprg kb2000
Version : WINDOWS:
Platform : WINDOWS
Issue type : kbbug