# Nested IF, two conditions?

##### New Member
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
Renewal 2/12/2011 2/12/2012

Thanks for any help.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### sr12345

##### Active Member
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

##### Legend
Try

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

#### Trouttrap2

##### Well-known Member
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

##### Well-known Member
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>
Code:
``````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)),"")``````

##### New Member
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.

Replies
22
Views
618
Replies
5
Views
160
Replies
14
Views
219
Replies
4
Views
192
Replies
3
Views
306

1,190,958
Messages
5,983,843
Members
439,866
Latest member
jh3268

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back