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.

Generic formula



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


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.


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

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.