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
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.