Respond Time SLA Calculation based on Working Date...

EugeneB · 05-30-2021 04:51 AM

Have a challenge here, to calculation the “Respond Time” SLA calculation based on the working day and working hours, understand that we have a WORKDAY() but what about working hours? (just say working hours is at 9AM to 6PM)

how can we do it? currently we do simply duration calculation
[RespondTime] - [Case Date] → [TicketRespondDuration]

Respond Time SLA is 8hours, (Duration: 08:00:00) then
if the [TicketRespondDuration] >= 08:00:00 → “Failed Respond SLA”
if the [TicketRespondDuration] <= 08:00:00 → “MeetRespond SLA”

anyone has an idea, how we could do it?

Suvrutt_Gurjar

If you share more examples of how you currently calculate and how you would like it to be calculated, the community could help you better. You may wish to further elaborate the following

Also any more considerations for weekend holidays etc.

In general, the work-related date time calculations can be tricky owing to such factors as holidays, working hours etc.

EugeneB

@Suvrutt_Gurjar might be later to add in holiday but now maybe we can start with workday and also working hour.

what we trying to achieve it, a case has been assigned at this datetime [Case Date] and then engineer responded the ticket at particular time datetime [RespondTime], hence we are using [RespondTime] - [Case Date] → [TicketRespondDuration] to calculate the ticket respond duration to see if it breach the 8 hours SLA respond time.

however, we want to calculate the duration, to exclude non working hours (if 9-6PM is the working hour). example,
[Case Date] 30/5/2021 04:00:00 PM
[RespondTime] 31/5/2021 09:00:00 AM
[RespondTime] - [Case Date] → [TicketRespondDuration]
[TicketRespondDuration] 17:00:00
using the above, if will return 17 hours duration, which consider breach of SLA. however if based on working hour (if 9-6PM is the working hour) it is only 02:00:00 hours which is within SLA

At the same time, i am trying to study the following from @Steve

Suvrutt_Gurjar

Thank you. Yes, @Steve has created several insightful tips and tricks on date time calculations. You may wish to take a look at those. Those are very useful/

Please post back if you still are looking for any assistance from community.

EugeneB

@Suvrutt_Gurjar not quite getting there, lolz. will need help from the community. i think the use case is slightly different

Suvrutt_Gurjar

Could you please update if you tried any expression?

EugeneB

Trying this method, if it is within 9AM to 6PM, then it will do simple deduct between Case and Respond time,

Anyone able to help, may refer to the post, on the logic we trying to achieve, - Respond Time SLA Calculation based on Working Date and Working Hours - #3 by EugeneB

if it is after office hour,

IF(
    ( 
      AND( DATETIME([Respond]) <= (DATETIME(DATE([Case])) + ("18:00:00" - "00:00:00")),
	        DATETIME([Respond]) >= (DATETIME(DATE([Case])) + ("09:00:00" - "00:00:00"))
          )
    ), 
    ([Respond] - [Case]), 
     ( 
     <<[A more complex login]>>
     )
   )

Suvrutt_Gurjar

Please try something like below. However, this will not work if there are any holidays in between the two fields of [RespondTime] , [Case Date] . However, I believe it will work for same day as well as other continuous work days.

HOUR([RespondTime]-[Case Date] ) - 15.00*(HOUR(DATE([RespondTime])-DATE([Case Date] ]))/24)

Assumptions: [RespondTime] , [Case Date] are DateTime type columns. The non working hours between two days are 15 ( 6 PM to next day 9 AM = 15 hours)

Edit: This will also work for rounded time. Will not consider minutes and seconds.

For a time in decimals and fractions you could try an expression something like below

TOTALHOURS([RespondTime]-[Case Date] )-15.00*(HOUR(DATE([RespondTime])-DATE([Case Date] ))/24)

EugeneB

@Suvrutt_Gurjar trying this logic on this link. what do you think? or if can done better? Respond Time SLA Calculation based on Working Date and Working Hours - #7 by EugeneB thinking the suggestion you share, that doesn’t define the 9 to 6pm right?

still figuring out the how to do the non workday, and after office hour excluding logic, if anyone has any suggestion feel free to share.