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

[Closed]
Report
-
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
-
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

1 reply


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:



Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
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
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!
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
24
As is typical, we'll probably never know.