Microsoft Office Tutorials and References

In Depth Information

**Date-Related Functions**

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))

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 non-date 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

twodigit day):

20100821

To convert this string to an actual date, you can use a formula such as this one, which assumes

the coded date is in cell A1:

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