Series of dates by year

An excel formula to series of dates by year

Related Functions

Sponsored Link

 Series of dates by year
=DATE(YEAR(date)+1,MONTH(date),DAY(date))

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)

Sponsored Link

0 votes. 0 / 5