# Need help with Formula.

#### yasser1983

##### New Member
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.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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

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>

 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.

That's good too, Teeroy.

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

--Data Bender

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.

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

Replies
1
Views
89
Replies
5
Views
81
Replies
6
Views
153
Replies
19
Views
338
Replies
2
Views
247

1,203,072
Messages
6,053,378
Members
444,660
Latest member
Mingalsbe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back