Series of dates by month

Series of dates by month

Generic formula 

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

Related formulas 

Series of dates by day

Explanation

To generate a dynamic series of dates with a formula that increases by one month from a single start date, you can use a formula based on DAY, MONTH, YEAR, and DATE functions.

How the formula works

In the example, B6 is the hard-coded start date and the formula in B7 is:

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

To solve this formula, Excel first extracts the year, month, and day values from the date in B6, then adds 1 to the month value. Next, a new date is assembled by the DATE function, using the same day and year, and month + 1 for month.

=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))=DATE(2010,1+1,15)=DATE(2010,2,15)=2/15/2010

The first formula therefore returns a new date of 2/15/2010, one month later than the starting date.

Once the first formula is entered, it is copied down as far as needed. Each subsequent formula creates a new date incremented by one day.

Note: if you start with a date like January 31, the formula above will skip February and move on to March. This happens because the new date, 2/31/2010 doesn't exist, so Excel uses the day value to roll forward to 3/3/2010, 3 days after the last day in February.

If you need a series of dates where every date is the last day of the month you can use EODATE like this:

=EOMONTH(B6,1)

Series of dates by month

Generic formula 

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

Related formulas 

Series of dates by day

Explanation

To generate a dynamic series of dates with a formula that increases by one month from a single start date, you can use a formula based on DAY, MONTH, YEAR, and DATE functions.

How the formula works

In the example, B6 is the hard-coded start date and the formula in B7 is:

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

To solve this formula, Excel first extracts the year, month, and day values from the date in B6, then adds 1 to the month value. Next, a new date is assembled by the DATE function, using the same day and year, and month + 1 for month.

=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))=DATE(2010,1+1,15)=DATE(2010,2,15)=2/15/2010

The first formula therefore returns a new date of 2/15/2010, one month later than the starting date.

Once the first formula is entered, it is copied down as far as needed. Each subsequent formula creates a new date incremented by one day.

Note: if you start with a date like January 31, the formula above will skip February and move on to March. This happens because the new date, 2/31/2010 doesn't exist, so Excel uses the day value to roll forward to 3/3/2010, 3 days after the last day in February.

If you need a series of dates where every date is the last day of the month you can use EODATE like this:

=EOMONTH(B6,1)