Let’s say you work in the IT department of a company, and part of your job is to keep track of when the warranty on various pieces of equipment expires. Ensuring that any necessary repairs or replacements are handled before the warranty runs out is crucial.
Your company has recently purchased several laptops, each with a three-year warranty period. The purchase date for these laptops is Jan 1, 2021. Your task is to calculate the expiration dates of these warranties.
For this you need to write a formula using the EDATE function. EDATE calculates a date that is a certain number of months (before or after) away from a date. It takes two arguments: the starting date and the number of months you want to add or subtract.
Add a 3 Years in a Date in Excel
Here we are using the date 1-Jan-2021 to make it more understandable to you, and you need to add three years to this date, so the date would be 1-Jan-2024.
For this, we will use the EDATE function.
- First, you need to EDATE function in cell B1.
- After that, in the first argument, refer to cell A1.
- Next, 12 for specifying 12 months.
- Now, multiply 12 by 3 to specify 3 years.
- In the end, enter the closing parentheses and hit enter.
=EDATE(A1,12*3)This formula adds 3 years to the date in cell A1 by moving forward by 36 months (12 months for each year, multiplied by 3). If the original date in the cells A1 is 01-Jan-2021, then it would return 01-Jan-2024.
Add 5 Years in a Date
Now in the same way, if you want to add 5 years to the date the formula would be something like this.
=EDATE(A1,12*5)If you have the date 01-Jan-2021 in the cell A1, it would return 01-Jan-2026 in the result which is five years ahead from the original date.
Adding Half a Year
This year adds six months to the date, adding half a year.
=EDATE(A1, 6)
If you have the date 1-Jan-24 in the cell A1, it would return 1-Jul-24 in the result which is 6 months ahead in from the original date.
Adding Fractional Years to a Date
EDATE is a flexible function that allows you to add fractional years to date. You don’t need to do anything extra but use a fractional value.
=EDATE(A1, 12*2.5)
This formula adds 2.5 years to the date in cell A1 by moving forward by 30 months (12 months for each year, multiplied by 2.5). If the date in the cell A1 is 1-Jan-24 then it will 01-Jul-26 in the result.
Subtract 10 Years from a Date
And if you want to subtract a year, let’s say ten years, you can use a formula just like below.
Make sure to have valid dates when ever you are trying to add or subtract a year from a date. If you have a date in the data which not a valid date as per Excel, all of these formulas will show an error in the result.
Other Method to Add and Subtract Years from a Date
You can also use a combination of (Date, Year, Month, and Day) functions to add or subtract years from a date.
In this formula:
- The year function gets the year from the date.
- The month function takes a month from the date and then adds five years (60 months) to it.
- The day function takes a day from the date.
- And the date function, in the end, creates a new date using input from all the functions.
=DATE(YEAR(A1),(MONTH(A1)+(12*5)),DAY(A1))This formula adds 5 years in the date in cell A1 by adding 60 months (12 months for each year). And,you can also use the same formula to subtract the years from a date.
=DATE(YEAR(A1),(MONTH(A1)+(-12*5)),DAY(A1))This formula subtracts 5 years from the date in cell A1 by calculating back 60 months (12 months for each year).
Custom Function to Add Years to a Date
Creating a custom function to add a specified number of years to a date in Excel might sound tricky, but it is simple and easy. Below is the simple VBA code that creates such a function.
Function AddYears(StartDate As Date, NumYears As Integer) As Date
AddYears = DateAdd("yyyy", NumYears, StartDate)
End Function
In this function, first argument is the StartDate, the date you want to use as the start date. And then, the second argument, NumYears, number of years you want to add to the start date.
- Press ALT + F11 to open the Visual Basic Editor editor.
- Right-click on any objects in the Project pane.
- Go to Insert and then click Module.
- Paste the Code in the Code Window of the Module.
- Close the Editor and Add the function into the worksheet.
Note – Custom functions do not update automatically when you change inputs unless the workbook calculation is automatic. If your function does not update, try recalculating the sheet by pressing F9.
Bonus Code
This is a code which you can use in Excel. With this code, you need to select the cell and run this code, it will show you an input box. In the input box, you need to enter the number of years you want to add to the date and then click OK.
It will change that date from the selected cell adding the number years you have mentioned in the date.
Sub AddYearsToDate()
Dim rng As Range
Dim cell As Range
Dim years As Variant
Dim inputValid As Boolean
years = InputBox("Enter the number of years to add:", "Add Years")
If Not IsNumeric(years) Or years = "" Then
MsgBox "Please enter a valid numeric value."
Exit Sub
End If
Set rng = Selection
For Each cell In rng
If IsDate(cell.Value) Then
cell.Value = DateAdd("yyyy", Val(years), cell.Value)
Else
MsgBox "The cell " & cell.Address & " does not contain a valid date.", vbExclamation
End If
Next cell
End Sub