Series of dates by year

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.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.