Creating a nested formula to calculate SLA metrics for ticketing system

[Alphasway]

Greetings,


I have yet to come across anything quite like this and I will try to be as thorough as possible...


I have been given a sheet of raw data that spans 6 months of time, consisting of 11 columns and 4000+ rows.


I am focused on 4 very specific columns:


"Ticket Priority" "Ticket Created Date" "Ticket Closed Date" and "Ticket Duration"


Each Ticket Priority has 4 categories: Level 1, Level 2, Level 3, and Level 4
Each category has a certain SLA metric, Level 1: 2 hours, Level 2: 4 hours, Level 3: 3 days, Level 4: 7 days
Some tickets do not have a closed date, and results as open in the Ticket Duration field and will display "#VALUE!" from the formulas I have created so far...


This is what I am trying to do and I don't know if it's possible...
From "Sheet1" I have been able to extract the following to simplify the raw data into 4 new columns onto "Sheet2":


Column 1 labeled Ticket Priority:
=IF(Sheet1!D2="1 Critical","P1",IF(Sheet1!D2="2 High","P2",IF(Sheet1!D2="3 Average","P3",IF(Sheet1!D2="4 Low","P4"))))


Column 2 labeled SLA Window
=IF(B2="P1","2 hours",IF(B2="P2","4 hours",IF(B2="P3","3 days",IF(B2="P4","7 days"))))


Column 3 labeled Ticket Runtime
=INT(Sheet1!F2-Sheet1!E2)&" days "&TEXT(MOD(Sheet1!F2-Sheet1!E2,1),"hh "" hours ""mm"" minutes""")


and now...
Column 4 labeled SLA Met / SLA Miss






I need help writing the last column's formula.


For example:


If the Ticket Priority is a "P3" the argument is for a "P3" the SLA is 3 days, the ticket creation date is 2/6/2013 12:13:48 PM, the ticket closed date is 2/6/2013 2:25:05 PM meaning the duration is 0d 2h 11m which means the SLA was not missed, the output field should simply say "SLA Met". However, if it is possible to put in a nested argument that if the Ticket Priority was a "P1" meaning the SLA is 2 hours then the field would register as "SLA Missed". that way I can keep a nested formula for all "P1-P4" tickets with their corresponding SLA windows to check against the total duration to see whether or not it meets the criteria of if the SLA was met or missed. I could then copy this single line and paste it down the entire column for all 4000+ rows.


A side note on Column 3, I literally stumbled upon this on another site on how to get the difference between two dates and I don't know have a clue on how it works exactly, all I know is that it does and I've left it alone.


If there is a neater and tidier way to clean up my current formulas I would also greatly appreciate any pointers or tips.






Warm Regards in advance,
-Alphasway

 

[phoenixdown]

I think you are supposed to create your own thread but I would do the following:

Remove the "days, hours and minutes" from the individual formulas as use Cell Formatting instead to give you that display.

Now that that those cells will be numbers, not text, you can do something like =A2 + B2 + C2 or whatever

Yeah changing the cell format to custom with the following works.
dd "days" hh "hours" mm "minutes" ss "seconds"