Excel: International Macro Changes Default Date and Time

• Microsoft Excel for Windows, versions 4.0 and 4.0a

SYMPTOMS

In Microsoft Excel version 4.0 for Windows, the date and time format used in your header and footer is determined by the country setting in the International option of the Microsoft Windows Control Panel. On your printed output and in print preview, the &D (date) and &T (time) settings in your header or footer will be displayed in the default format for the selected country. If, however, you are using a PRINT() or PRINT.PREVIEW() command from a macro sheet saved in the International Macro or the International Add-in format, the date and time in your header or footer is converted to the default US format.

STATUS

Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed above. This problem was corrected in version 5.0 of Microsoft Excel for Windows.

MORE INFORMATION

In Microsoft Windows versions 3.0 and 3.1, changing the country setting in the International option of the Control Panel will reset the date, time, and other number formats used by your Windows applications to the selected country's defaults. If, for example, you change the country to France, the default date format in Microsoft Excel will be DD/MM/YY. Consequently, the date code (&D) or the time code (&T) in your header or footer will be displayed in print preview and print in the DD/MM/YY or H:MM format.

When you run a macro from a normal macro sheet that executes a PRINT() or PRINT.PREVIEW() command, the date and time formats will also be displayed and printed in the selected country's default format. If, however, the macro sheet is saved in the International Macro or the International Add-in format, the date and time formats in print preview and on the printed output are printed as MM/DD/YY and H:MM AM/PM (the US default formats).

You are more likely to experience this problem if you are using a foreign language version of Microsoft Excel and the built-in international add-in macros. For example, if you have set your header or footer to show the current date or time and subsequently use VIEWS.XLA to define your print view and REPORTS.XLA to print it, the date and time formats will convert to the US defaults.

WORKAROUND

In many cases, saving the international macro sheet or international add-in to the normal macro or add-in format will resolve the problem. If you are using a foreign language version of Microsoft Excel, this process may require some translation. With the built-in international add-ins such as VIEWS.XLA and REPORTS.XLA, the extent of translation required to get the add-in to work properly may be prohibitive. If you are using these add-ins, your only workaround is to not use the date and time codes; instead type the full date or time in the appropriate format in the header or footer dialog boxes before you define the view for your report.

-or-

If you are working with macros that you created, precede the PRINT() or PRINT.PREVIEW() command with the appropriate command below.

To place the current date in the header, use:

   =PAGE.SETUP(TEXT(NOW(),"DD/MM/YY"))

   =PAGE.SETUP(,TEXT(NOW(),"DD/MM/YY"))

REFERENCES

"Microsoft Excel User's Guide 2," version 4.0, pages 297-299, 535