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

D4WNO

Board Regular
Joined
Mar 6, 2012
Messages
65
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")
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Maybe
Excel Formula:
=IF(AND(NETWORKDAYS(J2,TODAY())<3,E2="Open"),"Within KPI","Missed KPI")
 

D4WNO

Board Regular
Joined
Mar 6, 2012
Messages
65
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
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
I hadn't seen that you had edited your post, maybe
Excel Formula:
=IF(AND(NETWORKDAYS(J2,TODAY())<3,E2="Open"),"Within KPI",IF(OR(E2={"Pending","On hold"}),"Missed KPI","Closed"))
 

D4WNO

Board Regular
Joined
Mar 6, 2012
Messages
65

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
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")
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top