I would really appreciate your help on this one!

 

I am trying to write a formula which highlights any entry with subcategory of "server", where the difference between the date and time the call was opened and closed is greater than one hour.  This hour SLA should only be applicable during our service hours which is 8am - 6pm.

 

So far I have this:

 

TimeVar SetStart := TimeValue(08,00,00);     

TimeVar SetEnd   := TimeValue(18,00,00);

 

If{subcategory.description}="Server"
 AND (DateDiff("h",{request.opened_date_time},{request.closed_date_time})>1 

  AND time({request.opened_date_time}) IN SetStart TO SetEnd   

   AND time({request.closed_date_time}) IN SetStart TO SetEnd)  

    THEN ({request.closed_date_time};crRed)       

      ELSE ({request.closed_date_time};crNoColor) 

 

This works fine for the most part however it is not accurately capturing all the breached calls.  For example, there's a call which was opened before 8am, closed before 6pm but took longer than one hour to fix and this is not being highlighted.  I understand that there is a problem with the middle of my formula, where I'm specifying the service hours.  I need to capture all breaches beside those where the call was both opened and closed outside of our service hours.

 

It's a long one, but I hope it makes sense and I hope one of you lovely people can prevent me from pulling any more of my hair out.

 

Thanks!!

 

I think the fact that I am unfamiliar with creating formula is a severe handicap.  In essence we have numerous call categories and subcategories with their own associated SLAs like 1 hour, 1 day, 3 days etc.  I need to highlight all calls which have exceeded their SLA target, excluding calls which were both opened and closed outside of our service hours of 8am to 6pm.  Therefore a call opened at 7.30am but closed at 10.00 with an SLA target of 1 hour, should be highlighted as a breach whilst a call opened at 7pm and closed at 6am should not.

 

I hope this is a little clearer.

 

Thanks!

 

 

 

Thanks for your suggestion, which I tried.  Unfortunately it is highlighting calls which are not considered breaches eg call opened at 02.00am and closed at 08.30am.  As our service hours are 08.00am to 6.00pm, this call was effectively resolved within the SLA.

 

With regards to your next question, as long as the call is closed before 8:02 the next morning, it's within SLA.