XL2000: Format Function May Return an Incorrect Date (213514)

MORE INFORMATION

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, 1997 is represented as
   -----------------------------------------------
   month-day-year   7/5/97
   day-month-year   5/7/97
   year-month-day   97/7/5
				

Under Microsoft Windows 95/98 and Microsoft Windows NT, the first order, month-day-year, is used by the following regional settings:

   English (United States)
   Spanish (Dominican Republic)
   Spanish (Panama)
				

If you are using any of these three regional settings, the problems described in the "Symptoms" section in this article do not occur.

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 Start menu, point to Settings, and then click Control Panel.
2. Double-click the Regional Settings icon. Click to select the Regional Settings tab.
3. In the list of regional settings, click English (United Kingdom), and then click OK. When you are prompted, restart the computer.
4. Start Microsoft Excel 2000 and create a new workbook.
5. On the Tools menu, point to Macro, and then 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 then click Run.

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

• Cell A2, which should contain a date in the Long Date format (for example, Tuesday, August 12, 1997), contains a date in the Medium Date format (for example, 12-Aug-97).
• 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 14, 1998. 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, 1997, the date displayed in the cell is December 8, 1997.
• 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 date in cell A3, which is formatted by using the Medium Date format, appears correctly and has the correct value.

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