Exceljet

Quick, clean, and to the point

Series of dates by month

Excel formula: Series of dates by month
Generic formula 
=DATE(YEAR(date),MONTH(date)+1,DAY(date))
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)
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.