How to Create a Formula in Excel to Change Date by 1 Year (3 Methods)

In this tutorial, we will learn how to create a formula in excel to change the date by 1 year. While you are working with date type information you might need to change only the value of the year part from your date value. For this change, any specific year value can be added or subtracted from the date value. To illustrate the use of different functions in this article clearly, we will go to use a unique dataset for all the methods


Download Practice Workbook

You can download the practice workbook from here.


3 Quick Methods to Create a Formula in Excel to Change Date by 1 Year

Throughout this article, we will use 3 methods to create a formula in excel to change the date by 1 year. These methods will be executed by the use of the DATE function and the EDATE function.


1. Use the DATE Function to Generate a Formula in Excel to Change Date by 1 Year

First and foremost, we will use the Date function. The DATE function in Excel is used to generate a date by merging values (year, month, and day). In the following dataset, we have some date values in cells (B5:B10). We will add and subtract 1 year to these date values to change the date by 1 year.

Use DATE Formula in Excel to Change Date by 1 Year

So, let’s see the steps to add or subtract 1 year from a date value.

STEPS:

  • Firstly, select cell C5. Insert the following formula in that cell:
=DATE(YEAR(B5) + 1, MONTH(B5), DAY(B5))

Use DATE Formula in Excel to Change Date by 1 Year

  • Now, press Enter.
  • The above command adds 1 year with cell B5.

Use DATE Formula in Excel to Change Date by 1 Year

  • Secondly, select cell C5. Move the mouse cursor to the bottom right corner of the selected cell, where it will turn into a plus (+) sign, as shown below.
  • Thirdly, click the plus (+) sign and drag the Fill Handle down to cell C10 to duplicate the formula from cell C5. To get the same result, we can double-click on the plus (+) sign.

Use DATE Formula in Excel to Change Date by 1 Year

  • Then, free the mouse click.
  • So, we get all the dates of the cell range (C5:C10) is increased by 1 year in the cell range (D5:E10).

Use DATE Formula in Excel to Change Date by 1 Year

NOTE:

To subtract 1 year from the same values of dates, follow the below process.

STEPS:

  • Select cell C5 and insert the following formula:
=DATE(YEAR(B5) - 1, MONTH(B5), DAY(B5))

Use DATE Formula in Excel to Change Date by 1 Year

  • Now, hit Enter.
  • The above action subtracts 1 year from the date value of cell B5.

  • After that, select cell D5. Click on the Fill Handle tool and drag it to cell D10.

  • Drop the mouse click.
  • Lastly, we can see that all the value dates in the cell range (B5:B10) are subtracted by 1 year in range (D5:E10).

Read More: How to Create a Custom Formula in Excel (A Step-by-Step Guideline)


2. Create a Formula in Excel to Change Date by 1 Year with Excel EDATE Function

In the second method, we will perform the same task which is to create a formula in excel to change the date by 1 year. The only difference is this time we will use the EDATE function. The EDATE function generates a date that is a predefined number of months before or after the start date.

Change Date by 1 Year with Excel EDATE Formula

Let’s take a look at the steps to use the EDATE function in this method.

STEPS:

  • First, select cell C5. Insert the following formula in that cell:
=EDATE(B5,12)

Change Date by 1 Year with Excel EDATE Formula

  • Now, press Enter.
  • The above command adds 1 year with the date value of cell B5.

Change Date by 1 Year with Excel EDATE Formula

  • Next, pick cell C5. Slide the mouse pointer to the selected cell’s bottom right corner, where it will change into a plus (+) sign, as displayed below.
  • Then, to duplicate the formula from cell C5, click the plus (+) sign and drag the Fill Handle down to cell C10. We can get the same outcome by double-clicking on the plus (+) sign.

Change Date by 1 Year with Excel EDATE Formula

  • After that, release the mouse click.
  • So, we can see that all of the values of dates in the range (B5:B10) increase by one year in the cell range (C5:C10).

Change Date by 1 Year with Excel EDATE Formula

NOTE:

Here, follow the below steps to subtract 1 year from the same range.

STEPS:

  • In the beginning, select cell B5. Write down the following formula in that cell:
=EDATE(B5,-12)

  • Hit Enter.
  • The above operation subtracts one year from cell B5’s date value.

  • Next, select cell D5. Drag the Fill Handle tool to cell D10 by clicking on it and dragging it there.

  • Now, remove the mouse click.
  • Finally, we can observe that all the values of dates in the range (B5:B10) are subtracted by 1 year in the range (D5:D10).


3. Fill Series Option to Create a Formula in Excel to Change Date by 1 Year

We can also use the excel Fill Series option to change any date by 1 year. The excel Fill Series option fills the worksheet’s specified range of cells with a series of numbers, characters, or dates. In the following dataset, we have two same dates in cells B5 & C5. We will fill the cell range (B5:B10) by adding 1 year with each step. On the other hand, we will fill the cell range (C5:C10) by subtracting 1 year in each step.

Adjust Date by 1 Year with Excel Fill Series Option

So, simply follow the steps to conduct this operation.

STEPS:

  • Firstly, select the cell range (B5:B10).
  • Secondly, go to the Home From the Editing section of the ribbon click on the option Fill.
  • Then, select the option Series from the drop-down menu.
  • In short: Select range (B5:B10) > Home > Fill > Series

Adjust Date by 1 Year with Excel Fill Series Option

  • Now, a new dialogue box will open. In the box check the option Columns in the “Series in” section, Date in the Type section, Year in the “Date unit”.
  • Set the “Step value” as 1 and press OK.

Adjust Date by 1 Year with Excel Fill Series Option

  • So, we can see the cell range (B5:B10) is filled with date values now. If we notice we will see that each value in that range increases by 1 year from the previous value.

Adjust Date by 1 Year with Excel Fill Series Option

NOTE:

Now to subtract 1 year we have to go through the same procedures just for the cell range (C5:C10).

STEPS:

  • First, select the cell range (C5:C10).
  • Next, go to the Home Click on the option Fill from the Editing section of the ribbon.
  • Then, from the drop-down menu select the option Series.
  • In short: Select range (C5:C10) > Home > Fill > Series

Adjust Date by 1 Year with Excel Fill Series Option

  • We can see a new dialogue box now. In the box check the option Columns in the “Series in” section, Date in the Type section, Year in the “Date unit”.
  • Set the “Step value” to -1 and press OK.

  • As a result, the cell range (C5:C10) is now filled with date values. If we look closely we can see that each value in that range decrease by 1 year from the previous value.

Read More: How to Create a Formula in Excel without Using a Function (6 Approaches)


Conclusion

Therefore, this tutorial will show you how to create a formula in excel to change the date by 1 year. Use the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a comment below. Our team will make every effort to respond to you as quickly as possible. Keep an eye out for more creative Microsoft Excel solutions in the future.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo