Need help with Formula.

[yasser1983]

Hi Guys,

I would really appreciate If I could get help with excel formula.

I am trying to create a SLA report.
Tickets would have 3 types of severity:
Critical with SLA of 4 hours
Major with SLA of 8 hours
Minor with SLA of 72 hours.

SLA Priority	SLA Window	Start Date	End Date	SLA Status
Minor	72 hours	06/01/2014 13.10	06/01/2014 14.10
Major	8 hours	09/01/2014 10.15	09/01/2014 20.15
Critical	4 Hours	10/01/2014 19.00	12/01/2014 10.00

SLA status should be SLA Met or SLA not Met depending on whether the ticket was closed within the SLA or not.

Thank you in advance.

 

[Data Bender]

Hi, yasser1983.

This may not be the most elegant solution. (Time calculations can be tricky in Excel.) However, I believe it gets you closer to your goal.

SLA Priority	SLA Window (Hours)	Start Date	End Date	Time Open	SLA Status
Minor	=TEXT(72/24,"[hh]")	41791.5486111111	41791.5902777778	=TEXT(E4-D4, "[hh]:mm")	=IF(F4<=C4,"SLA Met","SLA Not Met")
Major	=TEXT(8/24,"[hh]")	41883.4270833333	41883.84375	=TEXT(E5-D5, "[hh]:mm")	=IF(F5<=C5,"SLA Met","SLA Not Met")
Critical	=TEXT(4/24,"[hh]")	41913.7916666667	41974.4166666667	=TEXT(E6-D6, "[hh]:mm")	=IF(F6<=C6,"SLA Met","SLA Not Met")

<tbody>
</tbody>

The result is as follows:

SLA Priority	SLA Window (Hours)	Start Date	End Date	Time Open	SLA Status
Minor	72	6/1/14 13:10	6/1/14 14:10	01:00	SLA Met
Major	08	9/1/14 10:15	9/1/14 20:15	10:00	SLA Not Met
Critical	04	10/1/14 19:00	12/1/14 10:00	1455:00	SLA Not Met

<tbody>
</tbody>

I hope this helps.

Good luck!

--Data Bender

 

[Teeroy]

Hi yasser1983,

This is not too different to Data Bender's solution but it determines the "SLA window" from the "SLA Priority" text and doesn't require a helper column ("Time Open").

Sheet1

	A	B	C	D	E
1	SLA Priority	SLA Window	Start Date	End Date	SLA Status
2	Minor	72 hours	6/01/2014 13:10	6/01/2014 14:10	Met
3	Major	8 hours	9/01/2014 10:15	9/01/2014 20:15	Failed
4	Critical	4 hours	10/01/2014 19:00	12/01/2014 10:00	Failed

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =LOOKUP(A2,{"Critical","Major","Minor";4,8,72}) E2 =IF((D2-C2)<=(B2/24),"Met","Failed") B3 =LOOKUP(A3,{"Critical","Major","Minor";4,8,72}) E3 =IF((D3-C3)<=(B3/24),"Met","Failed") B4 =LOOKUP(A4,{"Critical","Major","Minor";4,8,72}) E4 =IF((D4-C4)<=(B4/24),"Met","Failed") <tbody> </tbody>

<tbody>
</tbody>

Column B has a custom format of Type 0 "hours" to display correctly.

HTH.

 

[Data Bender]

That's good too, Teeroy.

How did you get those cool row and column headings into your table images?

--Data Bender

 

[Teeroy]

Hi Data Bender,

You can generate the html code by using Excel Jeanie HTML 4 excel add-in giving you the formulas and headings directly from your workbook.

 

[Data Bender]

Sweeeet. Thanks, Teeroy! I'll try to muddle through the German(?), or find an English alternative.