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.
Then generic formula syntax to get a series of dates by year is:
You need to insert the cell reference of date in Year, Month and Day function used in this formula.
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
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:
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:
Excel interprets it as:
Thereby giving the result as
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
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:
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