if a cell contains contains a specific text then display date +1,+2,+3Y

[Rodrigues]

Hi There
I'm looking for a formula to accomplish two points if possible:
1)
=IF(C7="Low",DATE(YEAR(G7)+3,MONTH(G7),DAY(G7))) - does work with one argument (Low), but I would like to add If(C7="Med"..., if(C7="High"..., so when shown Low=annualy, when shown Med=every 2 years, if High=every 3 years, example on sheet 1.

2)
Not sure if can use a formula or VBA, to create an calendar entry for each date shown under next preview cells.
Any help will be appreciated.

 

[Chihiro]

1) It would be something like below in I7.
=DATE(YEAR(G7)+MATCH(C7,$A$2:$A$4,0),MONTH(G7),DAY(G7))

2) Not sure what you mean.

 

[Rodrigues]

Hi There
I'm looking for a formula to accomplish two points if possible:
1)
=IF(C7="Low",DATE(YEAR(G7)+3,MONTH(G7),DAY(G7))) - does work with one argument (Low), but I would like to add If(C7="Med"..., if(C7="High"..., so when shown Low=annualy, when shown Med=every 2 years, if High=every 3 years, example on sheet 1.

2)
Not sure if can use a formula or VBA, to create an calendar entry for each date shown under next preview cells.
Any help will be appreciated.

Thanks Chihiro, excellent.
Ref Point 2, link/create an calendar (outlook 2010) entry (appointment) as the dates are populated on Next Preview columns (I ; L , etc).

Example:
Next preview date
I7 = 03/05/2016
I8 = 10/04/2018
I9 =10/05/2017

on my outlook 2010 calendar create an appoitment on above dates, as a subject we can use the names ones on cells B7, B8, B9 etc. (file with print screen).

 

[Chihiro]

2) It would require vba.

Have a look through the links.
http://www.slipstick.com/developer/create-appointments-spreadsheet-data/
http://officetricks.com/add-appointment-to-outlook-calendar-through-excel-macro-vba/
http://excel.tips.net/T007349_Getting_Excel_Dates_into_Outlooks_Calendar.html

 

[bosco_yip]

Or try this shorter formula for Point 1, to add 1-3 years to a date.

=EDATE(G7,MATCH($C7,$A$2:$A$4,0)*12)

Regards
Bosco

 

[Rodrigues]

Cheers.