# IF statement help - layering "and's" and "ors"

#### D4WNO

##### Board Regular
Hi, I hope you can help as it's driving me mad as I'm sure it's really simple to someone else. The excel help isn't pointing out where exactly the issue is and at this point, won't even let me post it.

I have some metrics, and I want my statement to show me the below:

J2 = Last updated date
E2 = Ticket status

1) I want it to look at all tickets with status (E2) of open, pending or on hold to show me if their last updated date is within the last 3 working days (networkdays).
2) If it is open and the date is within the last 3 days, show me "within KPI", otherwise "missed KPI".
3) If the status (E2) is something else other than my options then I guess it'll show me N/A (Unless I can get it to state "closed" or something too?

=IF(AND((NETWORKDAYS(J2,TODAY()>3,OR(E2="Open",E2="Pending",E2="On Hold")),"Missed KPI","Within KPI")

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Fluff

##### MrExcel MVP, Moderator
Maybe
Excel Formula:
``=IF(AND(NETWORKDAYS(J2,TODAY())<3,E2="Open"),"Within KPI","Missed KPI")``

#### D4WNO

##### Board Regular
Thank you, this does return a result, but pulls back "missed SLA" for anything that isn't set to open. It's also lost all of the OR statements.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=IF(AND(NETWORKDAYS(J2,TODAY())<3,E2="Open"),"Within KPI",IF(OR(E2={"Pending","On hold"}),"Missed KPI","Closed"))``

#### D4WNO

##### Board Regular

Oh weird, I don't think I edited.

This almost works, it needs to do the same calc if its open, pending, on hold etc (and "closed" for anything with any other status) - at the moment all the pending and on holds show as missed SLA. The more I stare at it the less it makes sense lol.

#### Fluff

##### MrExcel MVP, Moderator
at the moment all the pending and on holds show as missed SLA.
That's because you said
If it is open and the date is within the last 3 days, show me "within KPI", otherwise "missed KPI".
Maybe
Excel Formula:
``=IF(OR(E2={"Open","Pending","On hold"}),IF(NETWORKDAYS(J2,TODAY())<3,"Within KPI","Missed KPI"),"Closed")``

#### D4WNO

##### Board Regular
Aha, thank you so much!!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
2
Views
119
Replies
3
Views
210
Replies
11
Views
291
Replies
5
Views
843
Replies
2
Views
179

1,127,529
Messages
5,625,344
Members
416,096
Latest member
forevans

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