Microsoft KB Archive/904128

= The VBA function Application.International(xlMonthLeadingZero) returns a False value in the Japanese version of Excel =

Article ID: 904128

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition

-



SYMPTOMS
You experience the following behavior when you use the Japanese version of Microsoft Excel:  When you use the following Microsoft Visual Basic for Applications (VBA) function, the function returns a False value:

Application.International(xlMonthLeadingZero)

 The dates appear in the &quot;yyyy/m/d&quot; format instead of the &quot;yyyy/mm/dd&quot; format. That is, the leading zero does not appear in the months or in the dates that have a single digit. For example, the date &quot;June 3, 2005&quot; appears as &quot;2005/6/3&quot; instead of &quot;2005/06/03&quot;.



CAUSE
This issue occurs if the Short date format setting in Regional and Language Options is set to yyyy/mm/dd. The Japanese version of Excel ignores this setting. Instead, the Japanese version of Excel uses the &quot;yyyy/m/d&quot; format. Therefore, the VBA function returns a False value when the XlApplicationInternational constant xlMonthLeadingZero is used as the parameter for the International function.



WORKAROUND
To work around this issue, use one of the following methods.

Note The following methods affect the date format and how dates appear in the workbook. These methods do not work around the VBA issue in which the International function returns a False value.

Use a VBA macro to create the date format
You can use a VBA macro to set the number format of each cell that contains a date. For example, you can use the following macro to set the number format of cell A1 of a spreadsheet so that the date appears in the &quot;yyyy/mm/dd&quot; format.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Sub FormatA1 Range(&quot;A1&quot;).Value = &quot;2005/06/03&quot; Range(&quot;A1&quot;).NumberFormat = &quot;yyyy/mm/dd&quot; End Sub

Manually format the cells
To manually format the cells, create a custom number format, and then apply the custom number format to any cell in the workbook. To create a custom number format, follow these steps:
 * 1) On the Format menu, click Cells, and then click the Number tab.

Note In Microsoft Office Excel 2007, click Format in the Cells group on the Home tab, and then click Format Cells. In the Format Cells dialog box, click the Number tab.
 * 1) In the Category list, click Custom.
 * 2) In the Type box, type yyyy/mm/dd, and then click OK.

After you create the custom number format, apply this number format to any cell in the workbook. To do this, follow these steps:
 * 1) Click the cell that you want to format.
 * 2) On the Format menu, click Cells, and then click the Number tab.

Note In Excel 2007, click Format in the Cells group on the Home tab, and then click Format Cells. In the Format Cells dialog box, click the Number tab.
 * 1) In the Category list, click Custom.
 * 2) In the Type list, click yyyy/mm/dd.
 * 3) Click OK.



MORE INFORMATION
Excel uses the following procedure to determine the number format to use for dates. The number format is based on the date format settings in Regional and Language Options.
 * 1) Excel determines the order in which the year, the month, and the day appear in the Short date format setting in Regional and Language Options. No formatting is retrieved at this point.
 * 2) Excel retrieves the separator character. The separator character will be any one of the following characters:
 * 3) * A slash mark (/)
 * 4) * A hyphen (-)
 * 5) * A period (.)
 * 6) Excel determines whether the date that you typed in the cell uses the long date format or the short date format. Then, Excel retrieves the date format settings from Regional and Language Options.

The Japanese version of Excel uses the &quot;yyyy/m/d&quot; number format as the short date format if the following conditions are true:
 * Excel determines that the date is in the short date format.
 * The Short date format setting in Regional and Language Options is in the following order: year, month, and then day.

If both of these conditions are true, the Japanese version of Excel ignores the Short date format setting that is set in Regional and Language Options.

Keywords: kbexpertiseadvanced false italian application short date options language regional kbprb kbtshoot KB904128

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.