XL2000: Chart Axis May Be Four Years Early After You Format Scale

ID: Q214348

The information in this article applies to:

Microsoft Excel 2000

SYMPTOMS

When you format an axis in a chart by using a time scale in Microsoft Excel 2000, the axis may begin and end four years too early.

For example, if you want the axis to start at year 1990 and end at year 1999, it may actually start at year 1986 and end in year 1995.

For an example of this problem, see the "More Information" section in this article.

CAUSE

This problem may occur if the following conditions are true:

You enter a serial date value in the Minimum or Maximum box in the Scale tab in the Format Axis dialog box.

You are using the 1904 Date System.

NOTE: This problem is more likely to occur in Microsoft Excel 98 Macintosh Edition than in Microsoft Excel 2000, because Microsoft Excel 98 Macintosh Edition uses the 1904 Date System by default. The problem does not occur when Microsoft Excel uses the 1900 Date System.

Specifically, the problem occurs because Microsoft Excel uses the 1900 Date System for the serial date values you type in the Minimum and Maximum boxes.

WORKAROUND

To work around this problem, do not enter serial date values in the Minimum and Maximum boxes in the Scale tab of the Format Axis dialog box. Instead, type the dates as you would normally type them in a worksheet. For example, use the formats that are in the following table:


   Example            Note
   -------------------------------------------------------------------

   1/10/1998          Month-day-year order
   31/3/1998          Day-month-year order; for most non-United States
                      locales

   October 13, 1998   Fully spelled date
   16 October 1998    Fully spelled date

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Example

To see an example of this problem, follow these steps:

On the Tools menu, click Options. Click the Calculation tab. Click to select the 1904 date system check box, and then click OK.

Create a new workbook. In Sheet1, type the following values and formulas:


      A1:  3/31/90   B1:  1      D1:  =VALUE(A1)
      A2:  3/31/91   B2:  2
      A3:  3/31/92   B3:  3
      A4:  3/31/93   B4:  4
      A5:  3/31/94   B5:  5
      A6:  3/31/95   B6:  6
      A7:  3/31/96   B7:  7
      A8:  3/31/97   B8:  8
      A9:  3/31/98   B9:  9
      A10: 3/31/99   B10: 10     D10: =VALUE(A10)

The formula in cell D1 returns the value 31501, the serial date value for 3/31/90 in the 1904 Date System. The formula in cell D10 returns the value 34788, the serial date value for 3/31/99.

Select the range A1:B10. Press F11.

A new chart appears. This chart contains ten columns with values that range from 1 to 10. Below each column is a year (1990 to 1999).

Click to select the Category Axis at the bottom of the chart. On the Format menu, click Selected Axis. Click the Scale tab.

In the Minimum box, enter 31501. In the Maximum box, enter 34788. Then, click OK.

Note that the dates on the category axis extend from 1986 to 1995, not 1990 to 1999. Because of this, the last four columns do not appear in the chart.

On the Format menu, click Selected Axis.

Note that the dates in the Minimum and Maximum boxes are 1986 and 1995, not 1990 and 1999 as expected. If you re-type the dates as 3/31/1990 and 3/31/1999, the axis scale appears correctly.

Additional query words: XL98 XL2000

Keywords : xlformat xlchart
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug