Part II: Working with Formulas and Functions
260
The DATEVALUE function converts a text string that looks like a date into a date serial number.
The following formula returns 40412, which is the date serial number for August 22, 2010:=DATEVALUE(“8/22/2010”)To view the result of this formula as a date, you need to apply a date number format to the cell.Caution
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 for-
mats, but it returns an error for other regional date formats because Excel is looking for the eighth day of the
22nd month! n
Generating a series of dates .....................................................................................
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 12.4) — Fill Days, Fill Weekdays, Fill
Months, or Fill Years.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 cell’s fill handle while holding the right mouse button. In
the shortcut menu, choose Fill Series. Excel completes the series by entering additional dates, sepa-
rated by seven days.The advantage of using formulas (instead of AutoFill) to create a series of dates is that when you
change the first date, the others 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 enter the first date of the series into cell A1 and the for-
mula 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