Series of dates by year

Want to learn how to generate a dynamic series of the date that increases by one year from a single start date? This post will give you an overview of how to do so using the DAY, MONTH, YEAR, AND DATE function in Excel.

Formula

Then generic formula syntax to get a series of dates by year is:

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

You need to insert the cell reference of date in Year, Month and Day function used in this formula.

Example

To generate a series of the date that increases by one year from a single start date using the DATE function, follow the procedures below:

Using the example below,

  • First input the year, month and date value to start within C9,
  • Click on the next cell C10
  • Then type the Formula

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

The command will display as shown below.

Figure 1: The DATE function formula in Excel

 

  • Next, click on ENTER.

It will show you the result displayed in the screenshot below:

Figure 2: Result displayed

Interpretation of the Result

What is displayed above, shows that C9 contains the Start Date and the formula in C10  is:

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

To solve this equation, Excel first extracts year, month, and day values from a date value supplied in DATE function. It utilizes the same day and month values but adds 1 in year value to increase the year value by one. That is:

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

Excel interprets it as:  

=DATE(2001+1,2,20)

Thereby giving the result as

=DATE(2002,2,20)

However, the result is displayed as 2/20/2002 because of the DATE format.

The principal formula thus restores another date of 2/20/2002, one year later than the starting date.

Once the primary formula is entered, it is duplicated down as far as required. The subsequent formula produces a new date increased by one year, as shown in the screenshot below:

Figure 3: Series of date incremented by 1 year

Alternate Method

You can also alter this formula to satisfy the desired result. For instance, if you need a series of dates where each date is the first day of another year, you can utilize a formula like this:

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

Here date is starting date

How to change the date format in Excel

  • On the Home tab, in the Cells group, from the Format drop-down list, select Format Cells to display the Format Cells dialog box.
  • On the Number tab, in the Category list box, select Date to display the options for the date.

The dialogue box will display as shown below.

Figure 4: How to format DATE in Excel

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar