XL98: Format Function May Return an Incorrect Date

ID: Q182244

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In Microsoft Excel 98, if you run a Visual Basic for Applications macro that uses the Format function with a date, you may notice the following problems:

When you use the General Date format or the Short Date format, the month, day, and/or year of the dates may be switched.
For example, although the date January 10, 1998 may appear in the cell as 1/10/98, the actual value of the date is October 1, 1998 (10/1/98).
When you use the General Date format or the Short Date format, some of the dates appear as text, not as valid dates.
This problem may occur if the day or year of a date is greater than 12. For example, the date July 30, 1998 may appear in the cell as the text string "30/07/98" instead of the date 30/07/98.

CAUSE

These problems may occur if are using regional settings that use a date order of either day-month-year or year-month-day.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel 98 Macintosh Edition.

MORE INFORMATION

On the Macintosh, you can change regional settings by starting the Date & Time control panel and then clicking Date Formats. You can select a date format from the list of regions, or you can select a date format from the Short Date list.

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 the computer; these regional settings vary from country to country. Microsoft Excel mainly uses the three date orders that are listed in the following table.

   Order            July 5, 1998 is represented as
   -----------------------------------------------

   month-day-year   7/5/98
   day-month-year   5/7/98
   year-month-day   98/7/5

On the Macintosh, the first order, month-day-year, is used by default by only the U.S. regional setting. All other regional settings use a default setting of day-month-year.

If you are using any other regional settings and you run a Visual Basic macro that uses the Format function to insert dates into cells or to display a date in a message box, you may encounter the problems described in this article.

Example

You can demonstrate these problems by following these steps:

1. On the Apple menu, point to Control Panels, and click Date & Time.

2. Click Date Formats.

3. In the list of Date Formats, click Australian and then click OK. Close

   the Date & Time control panel.

4. Start Microsoft Excel 98 Macintosh Edition and create a new workbook.

5. On the Tools menu, point to Macro, and click Visual Basic Editor. Then,

   click Module on the Insert menu.

6. Type the following code into the new module:

      Sub Test()

          Range("A1").Value = Format(Date, "General Date")
          Range("A2").Value = Format(Date, "Long Date")
          Range("A3").Value = Format(Date, "Medium Date")
          Range("A4").Value = Format(Date, "Short Date")

      End Sub

7. On the File menu, click "Close and Return to Microsoft Excel."

8. On the Tools menu, point to Macro, and then click Macros. Click Test

   and click Run.

Dates are inserted into cells A1:A4 in the worksheet. Note the following behavior:

If the day of the month is 12 or less, select cells A1 and A4. On the Format menu, click Cells. Click the Number tab. In the Category list, click Date. In the Type list, click March 4, 1997. Then, click OK.
Note that in the dates in cell A1 and cell A4 the months and days are switched. For example, if today is August 12, 1998, the date displayed in the cell is December 8, 1998.
If the day of the month is more than 12, the dates appear as text strings instead of proper dates. You can see an example of this in cells A1 and A4.
The dates in cells A1 and A3, which are formatted by using the General Date and Medium Date formats, appear correctly and have the correct value.

NOTE: Be sure to switch the regional settings back to the default settings when you are done.

Additional query words: XL98 australian brasil british danish dutch finnish flemish french canadian german italian norwegian spanish swedish swiss french swiss german swiss italian

Keywords          : kbdta kbdtacode xlvbainfo OffVBA xlformat 
Version           : MACINTOSH:98
Platform          : MACINTOSH
Issue type        : kbprb

Last Reviewed: June 10, 1998