How to calculate SLA (weekends included)

Hi everyone

Can someone please help me?

I was looking for ideal function to calculate SLA. There are post regarding SLA calculation only for Mon-Fr within operational hours.

My problem is how to calculate SLA when SLA is met within 2 hours and where it is met within 48hrs

Operational Hours:
Mon-Fr 8:00 - 20:00
Sat and Sunday 09:00 - 17:00



For example:

Case 1:

If drop down 1, in column A1, is selected.

SLA is met if action is taken within 2 hours.

Case 2:

If drop down 2, in column A2 is selected.

SLA is met if action is taken within 48 hours.

There may be more cases, i.e. with different drop down options but even if they have a different name, the "Case" type SLA times are the same. i.e. 3 drop down options are case 1 types, 2 are case 2 types.


Any help at all is appreciated.

Thank you

Hi AliGW - no, I have the most recent excel.

Thanks for the welcome

Hi AliGW - please see sample workbook attached

Sorry- SLA means service level agreement time. As in by when the work needs to be completed.

Sorry- updated my profile and also attached spreadsheet in the above reply

Sorry- SLA means service level agreement time. As in by when the work needs to be completed.

I'll update my spreadsheet and will post it in a moment

Please see attached updated excel sample workbook.

I tried couldn't get it working with the weekends etc. I jerry rigged other formulas.

I tried to keep the columns the same so as to keep the formula working.

example of key dates and times are in column Q - V .


Sorry about the start to this email. Hopefully I covered everything.

Thank you for your patience

Sorry - please see updated manually calculated sample work sheet.

Also I have updated by profile - just using office pro 2019.

thank you. much appreciated.

Hi AliGW, I need a lookup that takes into consideration the operating hours:

Operational Hours:
Mon-Fr 8:00 - 20:00
Sat and Sunday 09:00 - 17:00

So when case 1 is selected and something comes in 10 mins before closing time, it deducts 10 mins from the over all "Time to complete". then allocates the remaining time to to the start of the next day.

The big issue is that sat and sunday have different start and finishing time

Sorry I am new to all of this.

Did I not show it in CASE 1? of the sample workbook v3? it crosses from a weekday to a weekend.

or case 3 - from weekend to weekday

There is a formula in row 8 down which the OP appears to have overwritten. Perhaps reinstate the formula (which appears to be a "standard" one for this type of problem) and there will a "starter" for us.

Column C is column B with the SLA hours added, allowing for weekdays, weekends and daily working hours.

Hi John, that is right. I had a formula in there before but it seems to only work on for weekdays and operates only 1 "Operating time"

I need 2 operating times and weekends.


here is the formula :

=IF(A2="","",WORKDAY(INT($B2),CEILING(MAX((INDEX($S$2:$S$9,MATCH($A2,$R$2:$R$9,0))-MAX($V$3-MAX($U$3,$B2-INT($B2)),0))/"8:00",0),1),$O$3:$O$10)+IF(MOD((INDEX($S$2:$S$9,MATCH($A2,$R$2:$R$9,0))-MAX($V$3-MAX($U$3,$B2-INT($B2)),0)),"8:00")>0,MOD(INDEX($S$2:$S$9,MATCH($A2,$R$2:$R$9,0))-MAX($V$3-MAX($U$3,$B2-INT($B2)),0),"8:00")+"9:00","17:00"))

Please find attached updated spreadsheet.