SLA Calculation for Helpdesk Resolution time

[Ferry Wils]

Dear Excel Gurus,

Some expert here was helped me with formulas but we have some changes. I need your help again to provide a formulas to calculate SLA time(Resolution time) for our Helpdesk ticket.

I've some basic conditions to calculating between 2 dates and times with the following condition:
- Working hours are from 08:00AM to 17:00PM
- Exclude for Lunch time: 12.00PM to 13.00PM
- Exclude Weekends.
- Exclude Public holiday(Holiday list)
- If ticket's Service type is a Problem, then calculate as date created - date closed.

Please Login or Register  to view this content.

Above Formula cannot works with below condition.
For below conditions, i wanted the calculation = date created - date closed.

1.If Ticket was opened after working hour & closed after working hour as well.
condition 1:
B2: 5/12/20 17:05 PM
C2: 5/12/20 17:09 PM

2. Ticket opened before working hour started & closed after working hour.
condition 2:
B2: 5/1/20 7:40 AM
C2: 5/4/20 17:16 PM

3. Ticket opened & closed between Lunch time.
condition 3:
B2: 11/12/20 11:23 AM
C2: 11/12/20 12:21 PM

Thanks again!

[JeteMc]

It may be helpful if we could see the manually calculated correct results for D2:D7 in addition to the incorrect results yielded by the formula.
Also examples of conditions 1 and 2 don't seem to be included, please include those along with expected results as well.
Let us know if you have any questions.

[Ferry Wils]

@JeteMc

I've update the result in D2:D7 manually, basically if 'Service type' = Problem, the SLA Calculation will still running even after office hour(date created - date closed.
However if Service type = Request, the exception will applied (SLA from 08.00 - 17.00, Lunch time 12.00-13.00).

Many Thanks

[Glenn Kennedy]

1. You make mention of a lunch break of 12:00 to 13:00. so... how is the elapsed time in row 2 correct. The resolution time is 13/11/2020 12:56:00, which is during lunchtime. So is the 1 hour for lunch to be COUNTED or IGNORED?

2. Please explain your expected answer at D3.

3. Please explain your expected answer at D5.

[JeteMc]

I feel that, as Glenn states, D3 and D5 are miscalculations.
The following will produce the expected results for the rest of the values given:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Glenn Kennedy]

But, do not forget to anser my 3 Qs... especially the first one, as I suspect you have made an error in your expected answers for Q2 & Q3 and are readily fixed.

[Ferry Wils]

Hi JeteMc,

D1 - should be 4 hours and the category as 'Request' (the SLA only caltulated during office hour (08.00AM - 17.00PM and exclude 1 hour Lunch time

D5 - correct 0:07 but using your new formula = 0:17

D8 - error & can't calculate using new formula, should be 0:04

If category = Problem, then calculation straight C3-B3. Thanks

[Ferry Wils]

[QUOTE=Glenn Kennedy;5454928]1. You make mention of a lunch break of 12:00 to 13:00. so... how is the elapsed time in row 2 correct. The resolution time is 13/11/2020 12:56:00, which is during lunchtime. So is the 1 hour for lunch to be COUNTED or IGNORED?

answer: SLA calculation started on next day, from 08.00AM to 12.00 PM and ignored 56 minutes

2. Please explain your expected answer at D3.

correct answer 0:33 minutes, from 4:27:00 PM to 5.00 PM

3. Please explain your expected answer at D5.

correct answer 0:07

Thanks

[Glenn Kennedy]

I still don't see how you get some of those expected results!!

Try this:
=IFERROR((NETWORKDAYS(B2,C2,HolidayList)-1)*($M$2-$L$2) + IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),$M$2,$L$2),$M$2)-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),$M$2,$L$2)+(NETWORKDAYS(B2,C2,HolidayList)-1)*($M$3-$L$3) + IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),$M$3,$L$3),$M$3)-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),$M$3,$L$3),"")

If this is not what you expected to see, please explain WHY your expected answers are different from those generated by this formula. Don't just say "It should be XX:YY". explain WHY.

[JeteMc]

I had not seen that Glenn was working on this and also I took a different approach.
I broke the calculations down into three parts as modeled in columns E:G
In the case where either the created time and resolution are on the same day OR the service type is a problem use:
=IF(ISNUMBER(SEARCH("request",A2)),SUM(MAX(0,MIN(MOD(C2,1),12/24)-MAX(MOD(B2,1),8/24)),MAX(0,MIN(MOD(C2,1),17/24)-MAX(MOD(B2,1),13/24))),C2-B2)
In the case where the created time and resolution are on different days AND the service type is a request:
For the first day use: =IF(AND(ISNUMBER(SEARCH("request",A2)),INT(B2)<>INT(C2)),IF(MOD(B2,1)<8/24,8/24,IF(MOD(B2,1)<12/24,SUM(12/24-MOD(B2,1),4/24),IF(MOD(B2,1)<17/24,17/24-MOD(B2,1),0))))
For the second day use: =IF(AND(ISNUMBER(SEARCH("request",A2)),INT(B2)<>INT(C2)),IF(MOD(C2,1)<12/24,MOD(C2,1)-8/24,IF(MOD(C2,1)<17/24,SUM(MAX(0,MOD(C2,1)-13/24),4/24),8/24)))
The values in column D are populated using: =SUM(E2:G2)
Let us know if you have any questions.

[Ferry Wils]

Hi JeteMC,

Let me try to use your formula first and let me check it. However for time being we consider this case closed.
Thanks a lot!!

Cheers
FW