XL97: Format Function May Return an Incorrect Date

• Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel, 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, 1997 may appear in the cell as 1/10/97, the actual value of the date is October 1, 1997 (10/1/97).



• 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, 1997 may appear in the cell as the text string "30/07/97" instead of the date 30/07/97.



• When you use the Long Date format, dates may appear in the Medium Date format instead.
  
  For example, if the date should appear as "Tuesday, August 12, 1997", it may appear as 12-Aug-97 instead.

CAUSE

These problems may occur if you 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 97.

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 

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

Example

1. On the Start menu, point to Settings, and click Control Panel.



2. Double-click Regional Settings. Select the Regional Settings tab.



3. In the list of regional settings, click "English (British)," and then click OK. When you are prompted, restart the computer.



4. Start Microsoft Excel 97 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.

• 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 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, 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.

Additional query words: XL97 sr1 sr-1 australian canadian new zealand french german italian british norwegian portuguese swedish danish ireland south africa argentina chile colombia paraguay uruguay peru mexican costa rica ecuador guatemala venezuela brazilian swiss icelandic indonesian afrikaans basque catalan dutch belgian finnish luxembourg austrian liechtenstein caribbean jamaica nynorsk bokmal

Keywords : kbdta kbdtacode xlformat KbVBA
Version : WINDOWS:
Platform : WINDOWS
Issue type : kbprb