If you have been adding years to a ‘date’ manually, it’s time to give your fingers some rest. Excel offers a set of powerful and efficient formulas that can handle date addition tasks effortlessly. In this blog, we will provide you with an easy guide to using these quick formulas to obtain calculated dates with just a few simple steps. By the end of this guide, you’ll be a pro at adding years to dates in Excel, saving you time and effort in your spreadsheet tasks.
We’ll cover three commonly used methods for adding years to a date in Excel. These methods are easy to understand and apply, making them valuable tools for any Excel user. Let’s dive in and explore these methods step by step.
The 3 Powerful Methods For Adding a Date to a Year are Explained Here
Let’s take a demo dataset as given below and consider a hypothetical situation in an organization. Due to the Pandemic, the joining date of 5 employees has been delayed. By adding years, let’s find the ‘new date of joining’ for them. We will be using the same dataset in all 3 methods for simplification of task.
Method-1 – Using EDATE Function
The general syntax for the EDATE formula will be:
=EDATE (start_date, months)
Step 1– There are four columns, ‘Name’, ‘Date of Joining’, ‘Delay’(in years), and ‘New Date of Joining’. First, insert these column names in row number ‘1’ and then input the respective data as given in the image below. Verify once after entering if the data perfectly matches.
On the above dataset, we will apply the EDATE formula. Let’s understand the use of EDATE with a simple example. We need to add the ‘Delay’ to ‘Date of Joining’ to get the result as ‘New date of Joining’.
Step 2– You need to enter the EDATE formula in cell D2. The formula should be written in the format: EDATE (initial date, months to add). So, the actual formula will be
=EDATE(B2, C2*12).
Note– In the ‘start_date’ section, you just have to input the cell name to which years will be added. In the ‘months’ section, you must input the cell name where the ‘number of years to be added’ is mentioned.
We must mention ‘*12’ to convert the years into months. You might already know that * means multiplication in Excel. Otherwise, the need for manual calculation will arrive again to calculate years in months.
Step 3– You will get the result in cell D2 after adding the ‘date’ of B2 along with the ‘number of delay years’ in C2. You will get the result below:
Step 4– Now select the D2 column; you will find a ‘+’ sign at the corner. Drag to row number 5 to fill the remaining cells. The ‘‘years’ from column ‘C’ will get added to the respective Dates in column ‘B’ for the same rows. You will get the following result:
Additional tip: You can easily use this formula to add fraction years as well. Eg: If the delay in years involve 2 years and 6 months as shown in cell C2. Convert the months into years (6/12) and apply the formula as shown below:
Method 2 – Using the DATE Function
The DATE function helps to obtain a result date based on individual dates, months, and years.
Here is the syntax for adding years using the DATE Function:
=DATE(YEAR([Cell Address])+[Number of years], MONTH([Cell Address]), DAY([Cell Address]))
Don’t get scared by the above syntax, DATE formula is actually very easy to use. If we write the above syntax according to our dataset, the formula would be written like this in cell D2:
=DATE(YEAR(B2)+C2, MONTH(B2), DAY(B2))
This formula seems longer than others mentioned here because it has been expanded into the Year-Month-Day format. This has allowed users to add years with a date and days and months too.
Step 1– If you don’t want to erase the above-implemented formula, create a new sheet in the same workbook. Then select all the cells having data. Press Ctrl+C and then go to the new sheet and press Ctrl+V.
Step 2– In our dataset, we are only concerned with the year, so in the formula mentioned above, ‘C2’ consisting of the delay years has only been added beside Year(B2). The formula should appear in this manner in cell D2:
Step 3– Click on the Enter key after adding the DATE formula, and the new date will appear with the years added:
Step 4– To fill the rest of the cells, drag the green box to the bottom cells whose date value you need to obtain:
Method-3 – Using the Custom formula
You can add years to a date using the simple addition formula. Here is the formula to execute:
=cell number of date + (number of years to add * 365)
This formula is the simplest hack to add years to a particular date. To execute this formula, you must be careful about the ‘*365’ section. 365 is multiplied to convert the number of years into the number of days.
Like the previous formulas, B2 here has the data, and C2 holds the number of delays converted into days. As this is a custom formula, we can only add days to a specific date, so converting years to days is necessary.
Step 1: The formula for our dataset needs to be written as follows in cell D2:
=B2+(C2*365)
Step 2– Press Enter sign after inputting the custom formula in cell D2 and get the resultant date after addition. Here you can witness that the result on D2 is one day less than in other methods. This is because 2020 was a leap year, and the other methods added one extra day automatically.
Step 3– To fill the rest of the cells, drag the corner ‘+’ button to the cells whose date value you need to obtain.
Note: Remember the leap years; you might need to alter the number of days for this customer formula. Be aware of the time periods you are working with when dealing with dates.
| Pro Tip- If you mix formats, such as formatting one cell as Date and another as General, Excel may not calculate the dates correctly. To ensure accuracy, make sure all date cells are formatted as ‘Date’ before adding years. |
Additional tip: You can easily use this formula to add fraction years as well. Eg: If the delay in years involve 2 years and 6 months as shown in cell C2. Convert the months into years (6/12) and apply the formula as shown below:
These were Excel’s three easiest ways to add years to a date. Each method has its advantages, and you can choose the one that suits your needs better. If you’re looking for a simple and straightforward approach, using basic arithmetic with the “DATE” function can be a great choice. On the other hand, if you need to add a whole number of years, the “EDATE” and custom function is a quick and efficient option.
No matter which method you choose, incorporating these techniques into your Excel repertoire will undoubtedly save you time and effort in managing date-related tasks. As you become proficient in date addition using these formulas, you’ll gain a valuable skillset that will prove useful in various spreadsheet applications
Remember to stay curious and explore other Excel date functions, as the software offers a wide range of powerful tools for managing dates, times, and data. With practice and experimentation, you can become a true Excel expert, streamlining your workflow and increasing your productivity
For learning new hacks and amazing Excel solutions, visit Tech Galaxy today!
Other articles you may also like: