How to Add Years to a Date in Excel Using a Formula?

Adding years to a date is a common task in Microsoft Excel that can be accomplished using formulas. Whether you need to calculate future dates for budgeting, scheduling, or other purposes, knowing how to add years to a date in Excel is an essential skill. In this article, we will cover the formulas and steps to easily add years to any date in Excel.

Using the DATE Function

One of the simplest ways to add years to a date in Excel is by using the DATE function. The DATE function allows you to create a date by specifying the year, month, and day as separate arguments.

Here’s how to use the DATE function to add years to a date:

1. In a cell, type the formula =DATE(
2. After the opening parenthesis, enter the year you want to add to. To add a specific number of years to the current year, use the formula YEAR(date)+number_of_years. For example, to add 5 years to the date in cell A1, you would use YEAR(A1)+5.
3. Type a comma after the year argument.
4. Enter the month number (1-12) for the resulting date. To keep the same month as the original date, use the formula MONTH(date). For example, MONTH(A1).
5. Type another comma.
6. Enter the day number (1-31) for the resulting date. To keep the same day as the original date, use the formula DAY(date). For example, DAY(A1).
7. Type a closing parenthesis to complete the DATE function.

Your final formula should look something like this:

=DATE(YEAR(A1)+5, MONTH(A1), DAY(A1))

This formula adds 5 years to the date in cell A1 while keeping the original month and day.

Examples

Adding Years with Simple Math

Another way to add years to a date in Excel is by using simple math. Since Excel stores dates as sequential numbers (with 1 being January 1, 1900), you can add a specific number of days to a date to get a future date.

To add years using this method:

1. Calculate the number of days in the years you want to add. There are 365 days in a regular year and 366 days in a leap year. For example, to add 3 years, you would calculate 365*3 = 1095 days (assuming no leap years).
2. In a cell, type the formula =date + days. Replace “date” with a cell reference or an actual date value, and replace “days” with the number of days you calculated in step 1.

For example, to add 3 years to the date in cell A1, you would use:

=A1 + 1095

Accounting for Leap Years

The simple math method works well for rough estimates, but it doesn’t account for leap years. To get more accurate results that factor in leap years, you can use the DATEDIF function to calculate the number of days between the original date and the future date.

Here’s how:

1. Set up your original date in one cell (e.g., A1).
2. In another cell, type the formula =DATE(YEAR(A1)+number_of_years, MONTH(A1), DAY(A1)). Replace “number_of_years” with the actual number of years you want to add.
3. In a third cell, type the formula =DATEDIF(A1, B1, “d”). This calculates the number of days between the original date and the future date, accounting for leap years.
4. Finally, add the formula =A1 + C1 in a fourth cell to get the future date that accurately adds the specified number of years.

Here’s an example:

Using the EDATE Function

Excel also provides a built-in function specifically for adding months to a date: EDATE. While it’s designed to work with months, you can also use it to add years by multiplying the number of years by 12.

Here’s how to use EDATE to add years to a date:

1. In a cell, type the formula =EDATE(
2. After the opening parenthesis, enter a cell reference or an actual date value for the starting date.
3. Type a comma.
4. Enter the number of years you want to add multiplied by 12. For example, to add 2 years, you would enter 24 (2 * 12).
5. Type a closing parenthesis to complete the EDATE function.

Your final formula should look something like this:

=EDATE(A1, 24)

This formula adds 2 years to the date in cell A1.

Examples

Handling Date Formatting

When using formulas to add years to dates in Excel, the resulting values will be formatted as dates by default. However, if you want to display the dates in a specific format, you can use Excel’s date formatting options.

To change the date format:

1. Select the cell(s) containing the date values.
2. Right-click and choose “Format Cells” from the context menu.
3. In the Format Cells dialog box, go to the “Number” tab.
4. Select “Date” from the category list.
5. Choose the desired date format from the Type list.
6. Click “OK” to apply the formatting.

Some common date formats include:

• M/d/yyyy (e.g., 6/15/2023)
• MM/dd/yyyy (e.g., 06/15/2023)
• d-MMM-yy (e.g., 15-Jun-23)
• dddd, MMMM d, yyyy (e.g., Thursday, June 15, 2023)

Tips for Working with Dates in Excel

Here are some additional tips for working with dates and adding years in Excel:

• When entering dates manually, use a consistent format to avoid confusion. Excel recognizes many common date formats, such as 6/15/2023 or 15-Jun-2023.
• To quickly enter today’s date in a cell, use the keyboard shortcut Ctrl + ; (semicolon).
• To enter a date that automatically updates to the current date whenever the worksheet is opened or recalculated, use the formula =TODAY().
• When copying formulas that reference dates, use absolute cell references (with $) if you want the date reference to stay constant, or use relative references if you want the date to change based on the row or column.

Final Thoughts

Adding years to dates is a common task in Excel that can be accomplished using various formulas and functions. The DATE function allows you to specify the year, month, and day to create a future date, while simple math can be used for rough estimates. For more accurate results that account for leap years, you can use the DATEDIF function in combination with DATE. The EDATE function provides a straightforward way to add months or years to a date. By mastering these techniques and understanding how Excel handles dates, you’ll be able to efficiently work with and manipulate dates in your spreadsheets.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.