Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles DATE SERIES Series of dates by year

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc