Series of dates by year

  • Post author:
  • Post category:Uncategorized

Excel has made working with dates simpler than ever before. We can generate a series of dates by year using the DATE function. The DATE function takes the YEAR, MONTH and DAY arguments. In this tutorial, we will learn how to generate a series of dates by year.

Figure 1: Example of How to Generate a Series of Dates by Year

Generic Syntax

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

How this Formula Works

In order for this formula to work, we first need a hard coded date. The Date function takes this date and extracts the year, month, and day values. YEAR (DATE) + 1 adds 1 to the year. With this information, the DATE function generates a new date with the same month and day but the next year. Thus, this formula generates a series of new dates by year.

Setting Up Data

The following example contains a hard coded date in cell B1.

Figure 2: The Hard-Coded Date to Work With

To generate series of dates by year in column B

  • Go to cell B3.
  • Click Formulas>Insert Function. In the window that pops up, type DATE in the search box and click Go.

Figure 3: Searching the DATE Function

  • This will open a pop up named Select a Function. Select the function DATE and press Ok.
  • Next, we need to provide the inputs for the YEAR, MONTH and DAY values. In this case, the values will be YEAR(B2)+1, MONTH(B2) and DAY(B2).
  • Now, we need to click OK. This will enter the formula =DATE (YEAR(B2)+1,MONTH(B2),DAY(B2)) to cell B3.
  • Finally, we will need to drag the formula from cells B3 to B8 to generate series of dates by year.

Figure 4: Providing the Arguments

  • Press Ok.

Figure 5: Completing the formula

Column B will now have a series of dates by year following the date in B2.

The DATE function is very handy working with dates. Here, with the help of this function we can generate a series of dates by year. By changing the MONTH and DAY parameters, we can also generate different dates.