Microsoft Office Tutorials and References

In Depth Information

Often, you want to insert a series of dates into a worksheet. For example, in tracking weekly sales, you may

want to enter a series of dates, each separated by seven days. These dates will serve to identify the sales figures.

In some cases, you can use the Excel AutoFill feature to insert a series of dates. Enter the first date and drag the

cell's fill handle while holding the right mouse button. Release the mouse button and select an option from the

shortcut menu (see Figure 6-3) — Fill Days, Fill Weekdays, Fill Months, or Fill Years. Notice that Excel does

not provide a Fill Weeks option.

For more flexibility, enter the first
two
dates in the series — for example, the starting day for week 1 and the

starting day for week 2. Then select both cells and drag the fill handle down the column. Excel will complete

the date series, with each date separated by the interval represented by the first two dates.

The advantage of using formulas (rather than the AutoFill feature) to create a series of dates is that you can

change the first date, and the others will then update automatically. You need to enter the starting date into a cell

and then use formulas (copied down the column) to generate the additional dates.

The following examples assume that you entered the first date of the series into cell A1 and the formula into

cell A2. You can then copy this formula down the column as many times as needed.

To generate a series of dates separated by seven days, use this formula:

=A1+7

To generate a series of dates separated by one month, you need a more complicated formula because months

don't all have the same number days. This formula creates a series of dates, separated by one month:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))