Microsoft Office Tutorials and References
In Depth Information
To view the result of this formula as a date, you need to apply a date number format to the cell.
Be careful when using the DATEVALUE function. A text string that looks like a date in
your country may not look like a date in another country. The preceding example works
fine if your system is set for U.S. date formats, but it returns an error for other regional
date formats because Excel is looking for the eighth day of the 22nd month!
Generating a series of dates
Often, you’ll want to insert a series of dates into a worksheet. For example, in tracking monthly
sales, you may want to enter a series of dates, each separated by one month. Or, maybe you
want a series of days with weekends omitted.
The most efficient way to enter a series of dates doesn’t require any formulas — just use Excel’s
AutoFill feature to insert the dates. Type the first date and then drag the cell’s fill handle while
pressing the right mouse button (that is, right-drag the cell’s fill handle). Release the mouse
button and select an option from the shortcut menu (see Figure 6-2).
For more flexibility, enter the first two dates in the series, and choose Fill Series from the shortcut
menu. For example, to enter a series of dates separated by seven days, enter the first two dates
of the series and select both cells. Drag the cells’ fill handle while holding the right mouse button.
In the shortcut menu, choose Fill Series. Excel completes the series by entering additional dates,
separated by seven days.
Figure 6-2: Using Excel’s AutoFill feature to create a series of dates.