Dates are made consecutive when you create charts in Microsoft Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q211767
When you chart a range of dates that are not consecutive, Microsoft Excel may fill in the missing dates to make the range consecutive.

Microsoft Excel 2000, Microsoft Excel 2002, and Microsoft Office Excel 2003

To resolve this behavior, format the axis to be a category axis instead of a time-scale axis. To do this, use one of the following methods.

Method 1

If you entered data on the worksheet but did not yet create the chart, follow these steps:
  1. Select the data on the worksheet that is to be the source data for the chart.
  2. Click Chart Wizard on the Standard toolbar.
  3. Make the changes you want in steps 1 and 2 of the Chart Wizard.
  4. In step 3 of the Chart Wizard, click the Axes tab.
  5. Click Category under Category (X) axis.
  6. Continue stepping through the Chart Wizard until the chart is created.

Method 2

If you already created the chart, follow these steps:
  1. Click the chart to select it.
  2. On the Chart menu, click Chart Options.
  3. Click the Axes tab.
  4. Click Category under Category (X) axes and click OK.

Microsoft Office Excel 2007

To resolve this behavior, format the axis to be a text axis instead of a date axis. To do this, follow these steps:
  1. Right-click the axis that you want to change, and then click Format Axis.
  2. In the Format Axis dialog box, click Axis Options.
  3. Under Axis type, click Text axis, and then click Close.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Excel automatically uses a time-scale axis if it detects that you have worksheet data that contains date number formatting in the first column or first row of source data for the chart, depending on the dimensions of your source data.

The time-scale axis is a special type of category axis that is organized by date. The time-scale axis in the chart displays the dates in chronological order even if the dates are not in that order on the worksheet.

The scaling of the time-scale axis is determined by an algorithm that Excel uses on the range of dates in the axis. The base unit is calculated by scanning through the series of points and by finding the smallest time difference between two date values in the range. This time difference then becomes the base unit for the time-scale axis.
XL2000 XL2003 XL2002 XL2007

Article ID: 211767 - Last Review: 01/31/2007 01:06:18 - Revision: 4.3

Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition

  • kbbug kbpending kbprogramming KB211767