Help to calculate 12 hour SLA, 7 days a week based over 10 hour opertaing day

[TracyLRoberts]

Hello, I would be most grateful for help, I am trying to calculate SLA for call attendance based on 7 days per week with operating hours of 08:00 - 18:00, SLA of 12 hours. The only solution I can find uses workday which then does not return correct weekend calculations, I feel so silly but can not figure this out Thanks in advance

Below, I have S as SLA,V as date time logged, and L as operating hours


=WORKDAY(V8,INT(S8/8)+IF(TIME(HOUR(V8),MINUTE(V8),SECOND(V8))+TIME(MOD(S8,8),MOD(MOD(S8,8),1)*60,0)>
$L$3,1,0),$K$2:$K$2)+IF(TIME(HOUR(V8),MINUTE(V8),SECOND(V8))+TIME(MOD(S8,8),MOD(MOD(S8,8),1)*60,0)>$L$3,$L$2
+TIME(HOUR(V8),MINUTE(V8),SECOND(V8))+TIME(MOD(S8,8),MOD(MOD(S8,8),1)*60,0)-$L$3,TIME(HOUR(V8),MINUTE(V8),SECOND(V8))
+TIME(MOD(S8,8),MOD(MOD(S8,8),1)*60,0))

 

[baitmaster]

Welcome to the board. No need to feel silly on this subject, I've done these calculations more times than I can remember and it still confuses me each time I have to do it again...

When you say you are trying to calculate SLA, what do you mean? Are you just trying to calculate the deadline time for any piece of work? Bear in mind this is an Excel page and industry-specific terminology can easily mislead

Couple of things to consider
1) a time in Excel is a value between 0 and 1, that uses cell formatting to look like a time (similarly, a date is just a whole number made to look like a date). If we work with the underlying number we can probably move away from all the confusion about calculating HOUR MINUTE, TIME and MOD
2) you have 10 hour days. Therefore any 12 hour time limit is going to be
a - one day later, 2 hours later, or
b - two days later, in the first 2 hours of the day, but only for calls that were received in the last 2 hours

So how do you want to present your result? is it in date + time format? If so the you just want a formula where, for start times <= 16:00 you add 1 + 2/24, and for start times > 16:00 you add 2 - 8/24. Rather than 1/12 and 1/3 you might want to use time-formatted inputs of 2:00 and 8:00