Microsoft KB Archive/281125

= Date field data is incorrect if you use Open or OpenText to open a .csv file in Excel 2000 =

Article ID: 281125

Article Last Modified on 12/3/2004

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q281125



IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry



SYMPTOMS
If you use the Open method or the OpenText method to open a Comma Separated Value (.csv) file in Microsoft Excel 2000 through a Microsoft Visual Basic for Applications macro, the dates appear incorrect.



CAUSE
This behavior occurs if the .csv file contains a date that is formatted in any other format than the Standard English (United States) format of M/d/yyyy.

Note This behavior will also occur if you change your system date setting to match the date format in the file that you are opening.



RESOLUTION
Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

To resolve this behavior, follow these steps:
 * 1) Close any programs that are running.
 * 2) On the Start menu, click Run, type regedit, and then click OK.
 * 3) In Registry Editor, expand the following key:


 * 1) On the Edit menu, point to New, and then click DWORD Value. With New Value #1 selected, type VBAAlwaysLoadUS, and then press ENTER.



MORE INFORMATION
By design, the behavior in Microsoft Excel 2000 is different from past versions of Excel. The change in how the OpenText method translates dates was completed to align Excel 2000 with International date standards. By default, this date translation behavior occurs because Excel always uses the M/d/yyyy format when you use the OpenText method in Visual Basic for Applications. Considering this fact, Excel interprets imported dates according to this standard.

Note If you open the .csv file manually, Excel will not use the default M/d/yyyy. Instead, it will use the language date format that is set for the operating system environment.

