Exceljet

Quick, clean, and to the point

Series of dates by year

Excel formula: Series of dates by year
Generic formula 
=DATE(YEAR(date)+1,MONTH(date),DAY(date))
Explanation 

If need to generate a dynamic series of dates with a formula that increase by one year from a single start date, you can do so with a formula that uses the 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)+1,MONTH(B6),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 year value. Next, a new date is reassembled by the DATE function, using the same day and month, and year + 1 for year.

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

The first formula therefore returns a new date of 1/15/2011, one year 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.

You can easily customize this formula if needed. For example, if you need a series of dates where every date is the first day of a new year, you can use a formula like this

=DATE(YEAR(date)+1,1,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.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables