Learn how to add years to a date in Excel in effortless ways.

Microsoft Excel’s unique date-processing system makes working with dates a bit challenging. To help you add years to a date value, I’ve tested several methods and found that the ones outlined in this article are the most practical and suitable for most datasets.

Follow the approaches demonstrated below to effortlessly calculate due dates, retirement dates, and investment maturity dates, or to just track birthdays.

Using Simple Arithmetic

This is the simplest method and is supported in most Excel editions.

For instance, you want to find out when the bonds in the above dataset will mature if the maturity is after 5 years of purchase.

Select C2 and enter the following basic Excel formula into the cell:

=B2+(5*365)

Hit Enter to calculate the maturity date.

If you need to add more or fewer years, simply increase or decrease the year multiplier, which is 5 in this example.

Now, click on the fill handle in the lower right corner of the cell C2 and drag it down until data exists in the adjacent columns, like column B.

Excel will replicate the formula in C2 and apply that to the rest of the cells in column C.

In most Excel editions, you won’t face any issues with the number formatting of the output values in column C.

However, if you see unusual outputs, select the cell ranges containing values, and press Ctrl + 1 to bring up the Format Cells dialog box.

Now, select Date below the Category column in the Number tab.

Choose a date format below the Type section on the right side of the dialog box.

Click OK to apply.

Excel will fix the output values.

The downside of this method is it doesn’t consider leap years.

Using the Ampersand Operator

Suppose, there are separate columns for date and years in your dataset. You want to add these two in a third column to show the full date as MM/DD/YYYY or DD/MM/YYYY format.

You can achieve that easily using the ampersand operator. However, you’ll also need to use the TEXT function to wrap the date and month values. Otherwise, Excel will convert the date into a numerical value and concatenate the year value to that, which is useless.

The sample dataset above contains dates in MM/DD format in column B and years as YYYY in column C.

Let’s consider, you want the first calculation to be done in D2. Select D2, and enter the following formula into the cell:

=TEXT(B2,"MM/DD")&"/"&C2

In the above formula, I’ve included the date values in the TEXT format so Excel adds the date and years as is without converting the dates to numeric values, like 45,296 for 01/05/2024.

Adjust the formula according to your own dataset. B2 is for MM/DD values and C2 is for YYYY values.

Hit Enter to concatenate the date and year.

Click on the fill handle of D2 and drag it down to replicate the formula and paste it into the rest of the cells of the column.

Excel will automatically add years to dates for the remaining dataset.

Consider another dataset where you’ve got the date entries in DD-MMM format, like 5-Jan.

To interlink such date entries with their corresponding year values in Excel, you can use the following formula in K2:

=TEXT(I2, "DD-MMM") & "-" & J2

Use the Enter key to calculate K2.

Now, use the fill handle of K2 to copy and paste the formula into the remaining cells.

Using the EDATE Function

The Excel EDATE function allows you to add years to a date in the form of months. Its formula starts with a starting value, the date to which you want to add a year. If you want to add 1 year, you must enter 12 as the Months argument of the function.

When you need to add multiple years, say 5 years, you need to multiply the Months argument value with 5 or any other multiplier you want.

You can add any number of months you want. So, if your data analytics task needs you to add a fractional year, say 1.5 years, to a date, you can add 18 months, and so on.

Consider the above dataset for practice. You’ve got the purchase dates of a few bonds in column B. You also know that all of these will expire after 5 years from the date when you bought them.

So, you want to get the maturity dates in column C. Select C2 and type in the following formula into the cell:

=EDATE(B2,5*12)

B2 is the cell reference for the Start_date argument and 5*12 is the input for the Months argument. So, you’re free to modify these entries in the formula to suit your own dataset.

Hit Enter to get the maturity date for the first bond.

Now, click on the fill handle square of C2 and drag it down until the cell up to which you want to copy and paste the same formula.

As you release the fill handle, Excel calculates the maturity dates of the remaining bonds by adding 60 months to all the referenced dates.

A drawback of this method is you can only find the EDATE function in Excel 2016 and newer editions.

Using the DATE Function

You can also use the DATE function to add a certain year to any date. Here, you’ll also use three other Excel date-time functions, like YEAR, MONTH, and DAY.

The idea is to extract the year component from a given date, add the number of years you want to add to the starting date, and compile all the values again as an MM/DD/YYYY date format.

Let’s consider you’ve got a project dataset as shown above in the example worksheet. You’d like to add the values mentioned in the Years column to the Start Date column to get the calculated values for the End Date.

So, go to the cell D2 of the End Date column. Type in the following formula into it and hit Enter.

=DATE(YEAR(B2) + C2, MONTH(B2), DAY(B2))

You’ll get the end date for the first project which is 6/15/2027, 7 years ahead of the start date value.

For your own dataset, customize the formula accordingly. You’ll mostly need to change the cell references since this is a dynamic function and none of the arguments have any hard-coded values.

Now, select D2 and drag the fill handle down to populate the end date for the rest of the projects.

Excel will automatically copy and paste the formula in the selected cells.

Using Power Query

Often, the date sheet datasets you’re analyzing might be huge and contain more rows than an Excel worksheet can handle. Not to mention, even if you separate the dataset into two parts and import that to a spreadsheet, Excel will become sluggish.

Here Power Query steps in to allow you to import unlimited numbers of rows and columns of raw data, transform those into a manageable size, and import to an Excel worksheet.

Also, this tool allows you to create a data transformation rule that you can run each time when importing a new dataset from an external source. No need to configure queries when datasets update.

If your database is on an external server, open an Excel workbook, and navigate to the Data tab.

Click on the Get Data command and choose a source from the context menu, like From Database.

Hover the cursor over it to get more specific database server sources, like SQL Server Database, Oracle Database, SAP HANA Database, etc.

In this tutorial, you can try importing the sample dataset shown above to Power Query, by selecting the dataset cell range and clicking the From Table/Range command in the Data tab.

You’ll see the Create Table dialog box. Click OK to confirm exporting the dataset to Power Query Editor.

So, the sample dataset contains the contract start dates of some employees. There’s also a legend showing how many years a contract lasts for interns, managers, and staff.

Now, you’re going to create a Power Query database that will automatically calculate the last dates for contract renewals depending on the input dataset.

When your dataset is in Power Query, go to the Add Column tab and hit the Custom Column command.

The Custom Column form will open.

In the New column name field, enter a preferred name, like Contract Renewal Date.

Now, copy and paste the following Power Query M code into the Custom column formula field:

= if [Role] = "Intern" then Date.AddYears([Contract Start Date], 2)
  else if [Role] = "Manager" then Date.AddYears([Contract Start Date], 5)
  else Date.AddYears([Contract Start Date], 3)

You’ll need to customize the variables of this formula as outlined below:

• The input column names, like Role and Contract Start Date
• Employee roles, like Intern, Manager, and Staff
• The contract durations, like 2, 5, and 3 years.

Ensure the Custom Column wizard shows the Now syntax errors... message at the bottom. Click OK to create the new column by adding the required years to the dates of the selected columns.

You can now delete the redundant Contract Renewal Date column which shows null values.

The new Contract Renewal Date.1 column will also show the time with the date entries.

Select the column, right-click, hover the cursor over the Transform menu, and select Date Only in the overflow menu.

Now, select all the columns, and click on the File menu.

Click Close & Load To…

You’ll see the Import Data dialog box.

Select the Existing worksheet option and select a destination cell or cell range on the active worksheet.

Power Query Editor will instantly export the transformed dataset to your designated cell range.

Congratulations! You’ve successfully added different year values to various date entries in Excel.

Conclusions

So far, you’ve gone through five different approaches to adding custom year values to any date entries in Excel.

If the article helped you to learn a new Excel skill, you can comment below to show your acknowledgment. Also, if you know a better method to add years to date which I might have missed in this Microsoft Excel tutorial, mention that in your reply.