Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

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

View products that this article applies to.

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 "yyyy/m/d" format instead of the "yyyy/mm/dd" 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 "June 3, 2005" appears as "2005/6/3" instead of "2005/06/03".

↑ Back to the top

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 "yyyy/m/d" format. Therefore, the VBA function returns a False value when the XlApplicationInternational constant xlMonthLeadingZero is used as the parameter for the International() function.

↑ Back to the top

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 "yyyy/mm/dd" 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("A1").Value = "2005/06/03"
        Range("A1").NumberFormat = "yyyy/mm/dd"
    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.
  2. In the Category list, click Custom.
  3. 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.
  3. In the Category list, click Custom.
  4. In the Type list, click yyyy/mm/dd.
  5. Click OK.

↑ Back to the top

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:
    • A slash mark (/)
    • A hyphen (-)
    • A period (.)
  3. 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 "yyyy/m/d" 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.

↑ Back to the top

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

↑ Back to the top

Article Info
Article ID : 904128
Revision : 5
Created on : 1/23/2007
Published on : 1/23/2007
Exists online : False
Views : 333