Excel is the pioneer spreadsheet tool we have been using for decades, and it still fulfills most of our requirements. There are several ways to populate data into the cells inside an Excel spreadsheet, and in some cases, the data includes a few columns of date. The good part is that Excel comes with some built-in functions regarding data, so if you want to add years to date in Excel, it will be extremely easy.
Since everyone has different requirements and there are different date formulae that you can use for the purpose, we have divided this step-by-step guide into multiple parts. Two of the most effective ways to do this is by:
- Performing arithmetic operations. The formula for performing an arithmetic operation to add one year in date in cell A1 is:
2. Date function (it is good when you want to specifically add to a specific section of date instead of adding in terms of days.) The formula for using the date function to add one year in date in cell A1 is:
Below are the details you need to know to easily change the data in an Excel spreadsheet.
Part 1. How to Add a Year to A Date in Excel?
The arithmetic operation method is the easiest way to add a year to any date data in an Excel spreadsheet. This method uses a simple way of adding 365 days to the date that you have entered. Since we use the date format and perform the operation on that, when 365 days are added, one year goes up in the date. In the formula, 1 multiplied by 365 tells how many years we want to add.
- Select a cell where you want the updated date to appear by clicking it.
- Go to the formula bar on top and type the formula "=E2+(1*365)" You will replace E2 with the cell that has an original date in your case. Now click anywhere, and a new date will appear.
Part 2. How to Add 2 Years to a Date in Excel?
Adding 2 years to any date is like adding one year. The most efficient method is using the arithmetic operation method since the formula is easier to type and requires much less time to track different cells. Now that you know how this formula works implementing it will be much easier with the steps below.
- Click the cell where you want the updated date to go. Clicking will select the cell, and you can move to the next step.
- In the top formula bar, type “=E2+(2*365)” Remember to replace E2 with your specific cell number. As you hit enter key, the updated date value will appear.
Part 3. How to Add 3 Years to a Date in Excel?
While you can also use the same arithmetic operation method here, let’s try a different method. Remember that in this method, you will be adding value to the year specifically. So, even if a leap year comes between the old and updated dates, it will be ignored since we keep the day and month the same.
The method is known as the date function method, and it works by adding a specific number to the given date data with the following steps:
- As always, we will click the target cell and then move to the formula bar on top.
- Type “=DATE(YEAR(E10)+3,MONTH(E10),DAY(E10))". In this case, the original data is present in cell E10. You must replace E10 with the cell that has this date in your case. Now click on some other cell to see the updated value.
Part 4. How to Add 5 Years to a Date in Excel?
Adding 5 years in the date is not different than any of the other methods discussed above. So, you can use any of the two methods discussed above. However, we will go with the date function method in our case. In the previous part, you will see that we add the number 3 to the year value in the formula. This formula allows us to add any number, and, in this part, we will add 5 in the steps below:
- Select your target cell where you want the updated date value to appear.
- Go to the formula bar above to type "=DATE(YEAR(E10)+5,MONTH(E10),DAY(E10))” Don’t forget to replace E10 with your original date value cell name. Now hit enter to see the updated value appear in the previously selected cell.
Part 5. How to Add 60 Years to a Date in Excel?
Say that you want to add a bigger number to the year. If you are looking for a more efficient method, the date function method will be better for adding many years. This method adds only to the years and does not change days or months. So, here is how you can do it:
- Start by clicking to select a target cell for the updated date value
- Click the formula bar on top and enter the formula “=DATE(YEAR(E10)+60,MONTH(E10),DAY(E10))". As the formula is implemented, you will see the updated date with 60 years added.
Part 6. How to Add 100 Years to a Date in Excel?
Since the easier and more efficient method for adding a bigger number of years to a cate inside an Excel spreadsheet is the date function method, we will be using that here as well. The process will be like the previous parts with the following steps:
- Select your target updated date cell by left-clicking on it. Now click the formula bar in the top menu.
- In the formula bar, you need to type the formula as it is "=DATE(YEAR(E10)+100,MONTH(E10),DAY(E10))". Now hitting enter will update the date value in the target cell.
Part 7. How to Add Years, Months, and Days to a Date in Excel?
Say that you want to add a specific number of days, months, years, or all of these to date simultaneously. The date function allows you to do that too. In the previous part, we only added a number to the year. Doing the same with month and day values will also allow us to update the day and date. Here are the steps and formula that you must follow to get it done:
- Select a cell on an Excel spreadsheet as the target cell. Your updated date will go to that cell.
- Go to formula bar and enter “=DATE(YEAR(E10)+3,MONTH(E10)+2,DAY(E10)+1)". Hit enter or click anywhere to see the updated date with all the changes you have made to this formula.
In this example, we added 1 today, making it 2; adding 2 to January made it March; similarly, adding 3 to 2001 made it 2004.
Part 8. Bonus Tip: How to Convert PDF to Excel Before Adding Years to Excel?
Editing dates when you receive the Excel or xlsx file is very easy. However, sometimes you may receive the spreadsheet document in PDF file format. That makes using special Excel formulae on the cells very difficult. Meanwhile, manual editing is not the solution. So, the only solution left for you is to try UPDF. It can help you in such scenarios with its instant solutions.
Windows • macOS • iOS • Android 100% secure
For this specific problem, we will use the Export PDF feature of UPDF, and here are the steps to follow:
- Open UPDF, click on Open File, and you need to select the PDF file from your PC through the browse screen. Alternatively, drag the file into the UPDF screen.
- As the file opens, you need to move to the right-side menu. Locate the Export PDF option and click on it.
- Now click on the Excel option to reveal the exporting option. Click on Export.
- UPDF will now open the browse screen where you can edit the file's name to be exported and its location. Finalize everything as per your requirements and proceed by hitting save.
- Wait and let UPDF convert your file. Within a few seconds, you will see the file appear on your PC's file manager.
Now you will have an Excel file with the proper file format. Hence, editing and using special formulae will become much easier.
Hopefully, with all these different methods and step-by-step guides, you know how to add years to date in Excel. Remember that even when using a formula, you don’t need to do things manually; instead, understand how the formula works. That way, you can add as many years to that date as possible.
The only thing that you may find tricky now is someone sending you spreadsheet data in PDF format. Still don’t need to worry since UPDF solves that issue too. Now you can instantly convert PDF to Excel without losing data or details. So, whether you want to add years to dates inside that PDF or want to do any editing, UPDF will make that easy for you.
Windows • macOS • iOS • Android 100% secure