Nested IF, two conditions?

[beadgirlsa]

I need help with either a nested IF statement, or another idea if there is a better way. I've been working on this for too long and could use some help.

If Column A returns the word "New" or "Renewal" and Column B>=2011, I would like Column C to return a date that is one year ahead of the date in column B. If Column A is "Upgrade", then nothing should be returned in Column C.

A B C
New 2/15/2011 3/15/2012
Upgrade 2/09/2011 (blank)
Renewal 2/12/2011 2/12/2012

Thanks for any help.

 

[sr12345]

Are the only options for column A "New", "Renewal", and "Upgrade"? Or are there other values, which we need to tell the equation to ignore?

 

[VoG]

Try

=IF(OR(A1="New",A1="Renewal"),DATE(YEAR(B1)+1,MONTH(B1),DAY(B1)),"")

 

[Trouttrap2]

Try this
=IF(OR(A3="new",A3="renewel"),DATE(YEAR(B3)+1,MONTH(B3),DAY(B3)),"")

First you have to parce out the date. That is where I used to get stuck on.

 

[texasalynn]

try this
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Renewal</TD><TD class=xl23 id=td_post_2640322 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num="40589">2/15/2011</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num="40954" x:fmla='=IF(AND(OR(A1="New",A1="Renewal"),YEAR(B1>=2011)),DATE(YEAR(B1)+1,MONTH(B1),DAY(B1)),"")'>2/15/2012</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Upgrade</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="40583">2/9/2011</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(AND(OR(A2="New",A2="Renewal"),YEAR(B2>=2011)),DATE(YEAR(B2)+1,MONTH(B2),DAY(B2)),"")'> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Renewal</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="40586">2/12/2011</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="40951" x:fmla='=IF(AND(OR(A3="New",A3="Renewal"),YEAR(B3>=2011)),DATE(YEAR(B3)+1,MONTH(B3),DAY(B3)),"")'>2/12/2012</TD></TR></TBODY></TABLE>

formula copy it down to all cells in column C
=IF(AND(OR(A1="New",A1="Renewal"),YEAR(B1>=2011)),DATE(YEAR(B1)+1,MONTH(B1),DAY(B1)),"")

 

[beadgirlsa]

Wow, thank you all so much. I was close, but didn't have the AND/OR in the right way in the equation.

These work, I've tested them.

THANK YOU.