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 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))                               Search JabSto ::

Custom Search