formula to determine if SLA was met

[excelDummy29]

Hi,

I have raw data and need a formula to determine if SLA was met.
I need it separated by same day, 1 day, 2 days , 3 days, 3+days from month to month.
I need it to then give me the percentage (out of %100) per category to see where SLA was met or not met.
I then need to show this in a stacked bar and line graph so Management can see our process.
Currently, I am using this formula to calculate the time difference between ticket open and closed date and time:

=NETWORKDAYS(K2,L2,$P$2:$P$9)-1-MOD(K2,1)+MOD(L2,1)

Please help!

Thanks,
Excel Dummy

 

[etaf]

thats quite a lot of questions , almost building the spreadsheet for you
have you already designed the sheet
i suspect a pivot table / chart may be required

do you have different sla's ?
you would need a column with what sla applied to that row
then if met / not meet - perhaps a 1 and 0 - so you can take a %

stacked bar graph against what x axis ? month
i would use closed date as month

not sure i understand the 3+ do you have an sla for that
or are you now plotting the number of days ?
same day, 1 day, 2 days , 3 days, 3+days
column showing number of days
assuming formula is in Q
using if (q2<1 , "sameday", if(q2<2, "nextday" ...........)

then in R
if met or not
again an if() i would use a 1 and 0
then you can use to create a %

what have you created so far