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!!

You started out saying you only wanted calls that occur during the service hours, but then say you want calls that are outside of service hours.  It would seem that you need to clearly define what your goal is, and then finding a solution will much easier.

 

HTH

 

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!

 

 

Switch it to using an OR statement when evaluating the time falling between your regular hours

 

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   

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

 

    THEN ({request.closed_date_time};crRed)       

      ELSE ({request.closed_date_time};crNoColor) 

 

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.

What do you do for the end of the day? Meaning if a call came in at 5:01 it will never be a breach because your day ended at 6:00...

 

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

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

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

 

If{subcategory.description}="Server"
 AND (DateDiff("h",

if time({request.opened_date_time}) < SetStart then date({request.opened_date_time}) + SetStart  else

if time({request.opened_date_time}) > SetEnd then dateadd('d',1,date({request.opened_date_time})) + SetStart  else {request.opened_date_time},{request.closed_date_time})>1 

 

 

 

DBlank, I managed to get you code working with a few tweaks which is wonderful.  Now I just got to work on specifying our working week, Mon-Fri.

 

Once again, thank you for your help.

If you run into problems let me know.