Calculate SLA

[c_guest]

Hi ,

I had this sheet created to calculate formula for SLA missed and SLA met. But unable to get a formula that would consider if the request is processed the next day was well - thats past SLA.

For examples : row 3 to 5 (in the sheet) all these 3 requests that were processed on the same day (3 May), within the SLA and its showing SLA met.

But the request which is in row 6 was a request received on 3 May but was processed on 7 May. And per the Col J it says SLA met because its only considering the difference of time when the request was received and the processed time.

I'm unable to get a formula. So that the days exceeded can also be considered.

So a formula that would say the request received in row 6 is SLA missed.

There are only 3 priorities set 2 hours (Urgent req), 4 hrs and 24 hrs.

Thanks

 

[p45cal]

You only have plain values in column H, and the values are incorrect.

 

[pecoflyer]

Is it important we know what an SLA is?

 

[GraH - Guido]

See in the last 3 columns if this is what you need?

 

[Peter Bartholomew]

If @GraH - Guido 's interpretation of the problem is correct, I would go for

= IF( (ProcessedDate+ProcessedTime) - (RequestDate+RequestTime) < PriorityHrs/24,
      "SLA met",
      "SLA Missed" )

or using the variables of LET to act as documentation / helper columns

= LET(
    ProcessedDateTime, ProcessedDate+ProcessedTime,
    RequestedDateTime, RequestDate+RequestTime,
    Difference, ProcessedDateTime - RequestedDateTime,
    IF( Difference < PriorityHrs/24,
       "SLA met",
       "SLA Missed" ))