SLA if formula

[Jgardner83]

Hi Guys,

I am trying to write a formula that picks up an SLA priority (P1, P2 P3 P4) and checks the time to resolution to tell me whether the SLA was within compliance.

I have the below formula so far (which is not working yet, I think it's the time format but can't get it working):

=IF(AND(E2="3",J2>= "00:12:00"),"Yes","No")

Where E2 is the priority number, J2 is the resolution time and 00:12:00 is the target SLA time for a P3.

J2 shows as 00:12:00 with time formated.

I would like to nest for all priorities, P1 = 2hrs, P2 = 4 hrs, P3 = 12 hrs, P4 = 20 hrs.

My Time to Resolution is using the following formula for resolution calculation to ensure business hours are taken into consideration:

=(NETWORKDAYS(G2,I2)-1)*("18:00"-"08:00")+IF(NETWORKDAYS(I2,I2),MEDIAN(MOD(I2,1),"18:00","08:00"),"18:00")-MEDIAN(NETWORKDAYS(G2,G2)*MOD(G2,1),"18:00","08:00")

Any help would be much appreciated!

 

[arthurbr]

In this formula =IF(AND(E2="3",J2>= "00:12:00"),"Yes","No") 3 is text as it is between double quotes.
A true number would be =IF(AND(E2=3,J2>= "00:12:00"),"Yes","No")