To work around this behavior, use the
Series command to fill large ranges with dates. To do this, follow these steps:
- Start Microsoft Excel, and create a new, blank workbook.
- Click the cell that you want to format with Date formatting.
- On the Format menu, click Cells.
- On the Number tab, click Date in the Category list.
- In the Type list, click the date format that you want, and then click OK.
- Type the number that you want in the cell that you formatted with the Date format, and then press ENTER.
- Click the cell from step 6.
- On the Edit menu, point to Fill, and then click Series.
Notice that the Series dialog box appears. - Under Series in, click Rows if you want to fill a row, or click Columns if you want to fill a column.
- Under Type, click Date, and then under Date unit, click the date format that you want.
- In the Step value box, type the number by which you want to increment the series.
- In the Stop value box, type the number that you want to use as the end value of the series.
- Click OK.
Notice that the series is filled with dates, and that the dates are incremented correctly throughout the series.