For calculation and data analysis, you may need to add years to a date in MS Excel. For example, to calculate the date of expiration of goods, to analyze the sales of a company after a period of time or to determine your policy’s payout date.
To do this, MS Excel provides several formulas to make this task easy to execute.
3 Methods to add years to a date in Microsoft Excel:
- Use the YEAR & DATE function
- Use the EDATE function
- Use a generic formula
Let us begin.
Method #1: Use the YEAR & DATE Function
This method is useful when you want to add years, months and days to a date simultaneously.
Step #1: Open your MS Excel workbook
Open the Excel sheet in which you have the desired date field and wherein you want to add years to a date.
Step #2: Place the cursor
Click on the cell where you would like to see the results of the formula.
You can confirm the cell by checking the Name box in the top left corner.
In our example, it is the cell next to A2.
Step #3: Enter the Excel formula
Type the Excel formula into the selected cell.
The formula is =DATE(YEAR(Cell Number)+X,MONTH(Cell Number),DAY(Cell Number))
where ‘X’ represents the number of years to be added.
For example, if you want to add 7 years, then the formula to be inserted is =DATE(YEAR(A3)+7,MONTH(A3),DAY(A3)).
Also, if you want to add 7 years, 7 months and 7 days to a date, then the formula will be =DATE(YEAR(A3)+7,MONTH(A3)+7,DAY(A3) +7).
Step #4: Press enter
Press enter to get the result for the first entry in your Excel sheet.
Once done, you can drag the cursor down the remaining cells next to the dates of purchase to get all the dates of expiration.
Method #2: Use the EDATE Function
This method is useful when you only need to add a year to the date.
Step #1: Open your MS Excel workbook
Open the Excel sheet in which you have the desired date field and wherein you want to add years to a date.
Step #2: Place the cursor
Click on the cell where you would like to see the results of the formula.
You can confirm the cell by checking the Name box in the top left corner.
In our example, it is the cell next to A2.
Step #3: Enter the Excel formula
Type the Excel formula into the selected cell.
The formula is =EDATE(Cell Number, (Y*12))
where ‘Y’ represents the number of years to be added.
For example, if you want to add 9 years, then the formula to be inserted is =EDATE(A2,(9*12))
Step #4: Press enter
Press enter to get the result for the first entry in the Excel sheet.
Once done, you can drag the cursor down the remaining cells next to the dates of purchase to get all the dates of expiration.
Method #3: Use a Generic Formula
This method is useful when you quickly need to calculate the additional years, as it is a super easy formula to remember. However for very important calculations where interest has to be calculated, for example, this quick generic formula is not to be used.
Step #1: Open your MS Excel workbook
Open the Excel sheet in which you have the desired date field and wherein you want to add years to a date.
Step #2: Place the cursor
Click on the cell where you would like to see the results of the formula.
You can confirm the cell by checking the Name box in the top left corner.
In our example, it is the cell next to A2.
Step #3: Enter the Excel formula
Type the Excel formula into the selected cell.
The formula is =Cell Number+(Z*365)
where ‘Z’ represents the number of years to be added.
For example, if you wish to add 6 years to a date, then the formula will be =A2+(6*365).
Step #4: Press enter
Press enter to get the result for the first entry in the Excel sheet.
Then, drag the cursor across all the dates of purchase to get all the dates of expiration.
Conclusion
You can use any of the above methods to quickly determine a future date. The cherry on the cake is that all the methods are simple and easy to use.