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

 

[MikeWx]

Using the information from your first spreadsheet in Columns D, E, and F; I calculated the duration as =F2-E2, formatting column G as d "days" hh "hours" mm "minutes". The table in columns J and K (you can move it further to the right if necessary) converts the Ticket Priority to a number of days and hours to determine if the SLA was met. What do you want the SLA to show if the ticket isn't closed?Sheet1

*	D	E	F	G	H	I	J	K
Ticket Priority	Ticket Created Date	Ticket Closed Date	Ticket Duration	SLA Met/ SLA Missed	*	*	*
2 High	SLA Met	*	1 Critical
*	*	*	*	*	*	2 High
*	*	*	*	*	*	3 Average
*	*	*	*	*	*	4 Low

<colgroup><col style="width: 30px;"><col style="width: 94px;"><col style="width: 97px;"><col style="width: 97px;"><col style="width: 225px;"><col style="width: 86px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 103px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: right"]6/2/2013 12:13[/TD]
[TD="align: right"]6/2/2013 14:25[/TD]
[TD="align: left"]0 days 02 hours 11 minutes[/TD]

[TD="align: right"]0 Days 02 Hours[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="align: right"]0 Days 04 Hours[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: right"]3 Days 00 Hours[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: right"]7 Days 00 Hours[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G2 =F2-E2 H2 =IF(G2<=VLOOKUP(D2,$J$2:$K$5,2,0),"SLA Met","SLA Missed") K2 =2/24 K3 =4/24 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[Snakehips]

Alphasway,

Welcome to MrExcel.


Perhaps try something like this.

The result in column E is independent of the data you have in columns C & D.
However, I have changed formula for column D to return 'Open' if no closed date.

Sheet1

*	D	E	F
Priority	Start	Finish
1 Critical
1 Critical
2 High
3 Average
4 Low
4 Low	*

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 90px"><COL style="WIDTH: 118px"><COL style="WIDTH: 129px"></COLGROUP>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]05/10/2013-15:18[/TD]
[TD="align: right"]05/10/2013-17:18[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]05/10/2013-15:18[/TD]
[TD="align: right"]05/10/2013-17:19[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]05/10/2013-15:18[/TD]
[TD="align: right"]05/10/2013-19:19[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]05/10/2013-15:18[/TD]
[TD="align: right"]08/10/2013-17:19[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]05/10/2013-15:18[/TD]
[TD="align: right"]12/10/2013-15:17[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]05/10/2013-15:18[/TD]

" target="_blank"> Excel Jeanie HTML 4 Sheet2

Excel Workbook
	D	E
2	0 days 02 hours 00 minutes	SLA Met
Sheet2

Hope that helps.

 

[Snakehips]

The Sheet2 image did not resolve itself correctly and I got timed out for editing.

Here is correction.....

Excel Workbook
	B	C	D	E
1	Ticket Priority	SLA Window	Ticket Runtime	SLA Met / SLA Miss
2	P1	2 hours	0 days 02 hours 00 minutes	SLA Met
3	P1	2 hours	0 days 02 hours 01 minutes	SLA Miss
4	P2	4 hours	0 days 04 hours 01 minutes	SLA Miss
5	P3	3 days	3 days 02 hours 01 minutes	SLA Miss
6	P4	7 days	6 days 23 hours 59 minutes	SLA Met
7	P4	7 days	Open	Open
Sheet2

 

[Alphasway]

Snakehips,

Thank you!!!

Here is a screenshot of your miracle work:

https://plus.google.com/photos/1138...s/5931477167941586945?authkey=CMW688Ok_czDigE

 

[Alphasway]

This data is almost 3+ months old, any existing "open" tickets will have missed SLA by a long shot unless other circumstances are freezing the ticket lifecycle such as a pending status Awaiting Vendor response.

I'm not going to worry too much about it, once again I greatly appreciate all your help.

With a turnaround in less that 24 hours, I couldn't ask for anything more!


Again,

Warm Regards,
Alphasway

 

[Alphasway]

MikeWx,

I like this too, it definitely gives me more options instead of seeing "#VALUE!" all over when I try to make something look "neat and organized" --

A lot of this data is 3+ months old, so no matter the priority of the ticket, unless extenuating circumstances are keeping these tickets frozen I would have to say they've all missed their SLA by a long shot.

However, as I begin to gather new data daily there may be a better formula to use to look at the date the ticket was opened and compare it to the date the report was run.
Also there are certain metrics that will freeze a ticket when we're in production, primarily it would be pending vendor action, pending customer response, or something else to put the ticket into a pending status, which again, will drive another factor into whether or not a ticket has met or missed SLA.

I greatly appreciate your help on this, and as I begin to pull new reports I'll definitely stay tuned here to MrExcel.


Again,

Warm Regards,
Alphasway

 

[Snakehips]

Snakehips,

Thank you!!!

Here is a screenshot of your miracle work:

https://plus.google.com/photos/1138...s/5931477167941586945?authkey=CMW688Ok_czDigE

Cheers, glad it helped.

 

[MikeWx]

MikeWx,

I like this too, it definitely gives me more options instead of seeing "#VALUE!" all over when I try to make something look "neat and organized" --

A lot of this data is 3+ months old, so no matter the priority of the ticket, unless extenuating circumstances are keeping these tickets frozen I would have to say they've all missed their SLA by a long shot.

However, as I begin to gather new data daily there may be a better formula to use to look at the date the ticket was opened and compare it to the date the report was run.
Also there are certain metrics that will freeze a ticket when we're in production, primarily it would be pending vendor action, pending customer response, or something else to put the ticket into a pending status, which again, will drive another factor into whether or not a ticket has met or missed SLA.

I greatly appreciate your help on this, and as I begin to pull new reports I'll definitely stay tuned here to MrExcel.


Again,

Warm Regards,
Alphasway

I'm glad it was helpful. Thanks for your response.

 

[Xer0923]

Hi,

I would like to up this thread. This is really helpful as I am creating my SLA at work. I used this formula:

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

And it gives me "days, hours and minutes".

Based on this example:

	B	C	D	E
Item received	Item Transferred	SLA Duration	Final Process Duration
[TABLE="width: 286"] <tbody>[TR] [TD="class: xl64, width: 286"][TABLE="width: 218"] <tbody>[TR] [TD="class: xl65, width: 218"][TABLE="width: 218"] <tbody>[TR] [TD="class: xl65, width: 218"]0 days 01 hours 18 minutes

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl65, width: 174"][TABLE="width: 236"]
<tbody>[TR]
[TD="class: xl65, width: 236"]12 days 18 hours 02 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"][TABLE="width: 236"]
<tbody>[TR]
[TD="class: xl65, width: 236"]30 days 03 hours 43 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD][TABLE="width: 286"]
<tbody>[TR]
[TD="class: xl65, width: 286"][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"]0 days 00 hours 32 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 236"]
<tbody>[TR]
[TD="class: xl65, width: 236"]7 days 15 hours 27 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"][TABLE="width: 236"]
<tbody>[TR]
[TD="class: xl65, width: 236"]3 days 08 hours 55 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD][TABLE="width: 286"]
<tbody>[TR]
[TD="class: xl65, width: 286"][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"]0 days 15 hours 05 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl65, width: 174"][TABLE="width: 236"]
<tbody>[TR]
[TD="class: xl65, width: 236"]9 days 21 hours 32 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"][TABLE="width: 236"]
<tbody>[TR]
[TD="class: xl65, width: 236"]3 days 05 hours 13 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SLA Miss[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"]0 days 04 hours 42 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl65, width: 174"][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl65, width: 174"][TABLE="width: 236"]
<tbody>[TR]
[TD="class: xl65, width: 236"]2 days 01 hours 29 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"][TABLE="width: 236"]
<tbody>[TR]
[TD="class: xl65, width: 236"]3 days 01 hours 18 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"]0 days 20 hours 03 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl65, width: 174"][TABLE="width: 236"]
<tbody>[TR]
[TD="class: xl65, width: 236"]7 days 00 hours 53 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"][TABLE="width: 236"]
<tbody>[TR]
[TD="class: xl65, width: 236"]6 days 23 hours 53 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD][TABLE="width: 286"]
<tbody>[TR]
[TD="class: xl65, width: 286"][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"]0 days 01 hours 10 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl65, width: 174"][TABLE="width: 236"]
<tbody>[TR]
[TD="class: xl65, width: 236"]1 days 02 hours 10 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 218"]
<tbody>[TR]
[TD="class: xl65, width: 218"][TABLE="width: 236"]
<tbody>[TR]
[TD="class: xl65, width: 236"]11 days 21 hours 44 minutes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Question:

1. How can I get the TOTAL number of "DD, HH, MM" in Column E (Final Duaration)? Columns B+C+D = E


Appreciate all Excel Gurus here who can answer my questions.


Thanks!
-Xer0923