# Add "x" years to date when it has certain words

Closed
TheAnalyst - Jun 9, 2016 at 03:10 PM
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 - Jun 11, 2016 at 10:56 AM
Hello,

I need help trying to add 5 or 10 years to a date if another column has this "word" or that "word"...

Hopefully I explained myself correctly.. Any help will be appreciated
Related:

## 1 response

Blocked Profile
Jun 9, 2016 at 06:20 PM
OK, so the format (syntax) for If as as follows:

=IF(LOGIC_TEST,TRUE,FALSE)

With that being said, you can nest IF statements, as in:

=IF(LOGIC_TEST_1,IF(LOGIC_TEST_2,TRUE_FOR2,FALSE_FOR2)
,FALSE_FOR1)

Date are no different than any other math with EXCEL! With that understanding, we can do something like:

We know that there are 365 Days in a year, and we want the number of days in a year.

So 365*5=1825

But in that period there is a leap year, with a leap day, so we must add 1.

So 1825+1=1826

What about the periods of time when you get two Leap years, for 2 leap days? It is possible to have a leap year in year one, and a leap year in year 5, as leap years are 4 years apart!

Anyway, I guess I am really asking how accurate do you want this solution? If you take the time to learn about year functions, Excel will do this for you.

But for this example, we are going to then add 1826 to our cell, to get a maturity date!

See Below:

RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Jun 9, 2016 at 09:02 PM
Can I recommend the use of the EDATE function here as it takes into account leap years.

For 5 years:
`EDATE(yourdate,60) `

For 10 years:
`EDATE(yourdate,120) `

So, to add 5 years to a date when a cell contains a "word":
`=IF(yourcell="word",EDATE(yourdate,60),"")`
Blocked Profile
Jun 10, 2016 at 10:08 PM
RayH, thank you!

Hopefully this is a lesson for all of those looking for this type of solution!

Don't be surprised when it doesn't work!
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Jun 11, 2016 at 10:56 AM
As is typical, we'll probably never know.