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

TracyLRoberts

New Member
Joined
Oct 7, 2019
Messages
1
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))
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top