Excel 2010 Bible

Chapter 12: Working with Dates and Times

261

FIGURE 12.4

Using AutoFill to create a series of dates.

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

because months don’t all have the same number of days. This formula creates a series of dates, sep-

arated by one month:

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

To generate a series of dates separated by one year, use this formula:

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

To generate a series of weekdays only (no Saturdays or Sundays), use the formula that follows.

This formula assumes that the date in cell A1 is not a weekend day.

=IF(WEEKDAY(A1)=6,A1+3,A1+1)

Converting a nondate string to a date ......................................................................

You may import data that contains dates coded as text strings. For example, the following text

represents August 21, 2010 (a four-digit year followed by a two-digit month, followed by a two-

digit day):

20100821

To convert this string to an actual date, you can use a formula, such as the following. (It assumes

that the coded data is in cell A1.)

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))