SLA Calulation excluding weekends

B

big40ish

New Member
Joined
Nov 10, 2018
Messages
4
[TABLE="width: 3021"]
<colgroup><col span="2"><col><col span="2"><col><col><col span="11"></colgroup><tbody>[TR]
[TD]Reported Date[/TD]
[TD]Day of the Week[/TD]
[TD]SDT Number[/TD]
[TD]Machine Type[/TD]
[TD]Serial Number[/TD]
[TD]Reported Fault[/TD]
[TD]Status[/TD]
[TD]Action Taken[/TD]
[TD]Call Out Response Date[/TD]
[TD]Call Out Response Time[/TD]
[TD]Job Completion Date/Time[/TD]
[TD]Machine Down Time[/TD]
[TD]Engineer[/TD]
[TD]Area[/TD]
[TD]Reason for unit still OOS[/TD]
[TD]Additional info[/TD]
[TD]SLA[/TD]
[TD]SLA MET/NOT MET[/TD]
[/TR]
[TR]
[TD]21/10/2018 22:43[/TD]
[TD]Sunday[/TD]
[TD]SDT23578[/TD]
[TD]Ionscan 400B[/TD]
[TD]31207[/TD]
[TD]Unable to verify[/TD]
[TD]Not complete[/TD]
[TD]Verific but wont print out[/TD]
[TD]22/10/2018 15:30[/TD]
[TD]16:47:00[/TD]
[TD]31/10/2018 23:59[/TD]
[TD]241:16:59[/TD]
[TD]Neil Redmond[/TD]
[TD]T2 lane 8[/TD]
[TD]Parts required[/TD]
[TD]comms board [/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22/10/2018 03:56[/TD]
[TD]Monday[/TD]
[TD]SDT23641[/TD]
[TD]Ionscan 400B[/TD]
[TD]12513[/TD]
[TD]Unable to verify[/TD]
[TD]Not complete[/TD]
[TD]Slide tray damaged[/TD]
[TD]22/10/2018 16:00[/TD]
[TD]12:04:00[/TD]
[TD]31/10/2018 23:59[/TD]
[TD]236:03:59[/TD]
[TD]Neil Redmond[/TD]
[TD]T2 Staff[/TD]
[TD]Parts required[/TD]
[TD]New slide tray[/TD]
[TD]0[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22/10/2018 13:49[/TD]
[TD]Monday[/TD]
[TD]SDT23646[/TD]
[TD]Ionscan 400B[/TD]
[TD]12515[/TD]
[TD]Unable to verify[/TD]
[TD]Complete[/TD]
[TD]Sample inlet and APT reset[/TD]
[TD]23/10/2018 11:45[/TD]
[TD]21:56:00[/TD]
[TD]23/10/2018 12:14[/TD]
[TD]22:25:00[/TD]
[TD]Neil Redmond[/TD]
[TD]T1 Lane 8[/TD]
[TD]N/A[/TD]
[TD]BIS[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23/10/2018 07:36[/TD]
[TD]Tuesday[/TD]
[TD]SDT23659[/TD]
[TD]Ionscan 400B[/TD]
[TD]25609[/TD]
[TD]Unable to verify[/TD]
[TD]Complete[/TD]
[TD]Sample inlet and APT reset[/TD]
[TD]23/10/2018 12:30[/TD]
[TD]4:54:00[/TD]
[TD]23/10/2018 12:45[/TD]
[TD]5:09:00[/TD]
[TD]Neil Redmond[/TD]
[TD]T1 Bravo Lane 18[/TD]
[TD]N/A[/TD]
[TD]BIS[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22/10/2018 23:48[/TD]
[TD]Monday[/TD]
[TD]SDT23658[/TD]
[TD]Ionscan 400B[/TD]
[TD]33098[/TD]
[TD]Unable to verify[/TD]
[TD]Complete[/TD]
[TD]Sample inlet and APT reset[/TD]
[TD]23/10/2018 15:00[/TD]
[TD]15:12:00[/TD]
[TD]23/10/2018 15:20[/TD]
[TD]15:32:00[/TD]
[TD]Neil Redmond[/TD]
[TD]T2 Lane 8[/TD]
[TD]N/A[/TD]
[TD]BIS[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30/10/2018 00:05[/TD]
[TD]Tuesday[/TD]
[TD]SDT23679[/TD]
[TD]Ionscan 400B[/TD]
[TD]33098[/TD]
[TD]Calibrant not stable[/TD]
[TD]Complete[/TD]
[TD]APT reset & Ref peak adjust[/TD]
[TD]30/10/2018 16:15[/TD]
[TD]16:10:00[/TD]
[TD]30/10/2018 17:15[/TD]
[TD]17:10:00[/TD]
[TD]Neil Redmond[/TD]
[TD]T2 Lane 6[/TD]
[TD]N/A[/TD]
[TD]BIS[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]31/10/2018 10:54[/TD]
[TD]Wednesday[/TD]
[TD]SDT23730[/TD]
[TD]Ionscan 400B[/TD]
[TD]31207[/TD]
[TD]Loose slide tray assembly[/TD]
[TD]Complete[/TD]
[TD]Refitted slide tray assembly[/TD]
[TD]31/10/2018 12:00[/TD]
[TD]1:06:00[/TD]
[TD]31/10/2018 12:30[/TD]
[TD]1:36:00[/TD]
[TD]Neil Redmond[/TD]
[TD]T2 Lane 6[/TD]
[TD]N/A[/TD]
[TD]BIS[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]26/10/2018 22:47[/TD]
[TD]Friday[/TD]
[TD]SDT23695[/TD]
[TD]Ionscan 400B[/TD]
[TD]25609[/TD]
[TD]Unable to verify[/TD]
[TD]Complete[/TD]
[TD]Sample inlet and APT reset[/TD]
[TD]31/10/2018 14:15[/TD]
[TD]111:28:00[/TD]
[TD]31/10/2018 14:45[/TD]
[TD]111:58:00[/TD]
[TD]Neil Redmond[/TD]
[TD]T1 Bravo Lane 18[/TD]
[TD]N/A[/TD]
[TD]BIS[/TD]
[TD]4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]30/10/2018 05:17[/TD]
[TD]Tuesday[/TD]
[TD]SDT23720[/TD]
[TD]Ionscan 400B[/TD]
[TD]32549[/TD]
[TD]False activations[/TD]
[TD]Complete[/TD]
[TD]Clean and condenser filter[/TD]
[TD]02/11/2018 09:00[/TD]
[TD]75:43:00[/TD]
[TD]02/11/2018 10:30[/TD]
[TD]77:13:00[/TD]
[TD]Neil Redmond[/TD]
[TD]T3 Service yard[/TD]
[TD]N/A[/TD]
[TD]BIS[/TD]
[TD]4[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


Ok I need help as i am not good with excel!! :laugh::laugh:

I need a sla met/not met to match the following criteria :

Reported Date needs to be attended within 24hrs, but excludes weekends- We only work between hrs 07:30 - 17:00

Help!!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
If ive understood try:

=IF(I2>(WORKDAY(A2,1)+MOD(A2,1)),"NOT MET","MET")

You dont mention the rules for the time so ive just used 24 hours after the reported time ignoring the weekend (ie a call at 9 pm on a friday needs to be serviced by 9 pm monday).
 
Upvote 0
Hi

Thanks for the reply, the rules is, if the 'reported date' time is between 07:30 - 17:30 then we meet the SLA.

I also need to look at if 'job completion date/time' If the machine needs parts , i need to know how long it has taken to replace the parts> Can you help with this column
 
Upvote 0
Hi,
Firstly I would make a few assumptions as I was a little confused about the meaning of “attended”
We are comparing date times between Reported Date (Column “A”) and Call Out Response Date (Column “I”)
The working day is between 07:30 and 17:30 = 10 hours
When you say “attended within 24hrs”, you really mean 24 working hours (not real hours)

Create some Named Ranges as shown below and enter the start and end times into the cells. The Named Ranges can be on the same page or on a different page, whichever you prefer.
StartTime = 07:30
StartLun = 13:00
EndLun = 13:30
EndTime = 17:30
If you do not want to have a lunch period, enter 13:00 into both the StarLun & EndLun cells so it will return a zero lunch period.

On your main page I would recommend using another column for the duration,
So use column “R” and add a header name of “Duration”, format the column as
:mm:ss
In cell “R2” use this formula and copy down.

Code: 
=IF(OR(A2="",I2=""),"-",IF(A2>I2,"Wrong Date",(NETWORKDAYS(A2,I2)-1)*(EndTime-StartTime+StartLun-EndLun)+IF(NETWORKDAYS(I2,I2),MEDIAN(MOD(I2,1), EndTime,StartTime)-MEDIAN(MOD(I2,1), EndLun,StartLun),EndTime-EndLun)-IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1), EndTime,StartTime)-MEDIAN(MOD(A2,1), EndLun,StartLun),StartTime-StartLun)))

In column “S” called “SLA Met / Not Met”, use this formula in cell “S2” and copy down.
Code: 
 =IF(R2<1,"Met","Not Met")

If you want to do the same thing for job completion column “K” use the same formula but adjust the formula and change all references from column ”I” to column “K”
This is assuming you wish to compare column “A” to column”K”.

Hope this helps
Paul.
 
Upvote 0
Taul said:
Hi,
Firstly I would make a few assumptions as I was a little confused about the meaning of “attended”
We are comparing date times between Reported Date (Column “A”) and Call Out Response Date (Column “I”)
The working day is between 07:30 and 17:30 = 10 hours
When you say “attended within 24hrs”, you really mean 24 working hours (not real hours)

Create some Named Ranges as shown below and enter the start and end times into the cells. The Named Ranges can be on the same page or on a different page, whichever you prefer.
StartTime = 07:30
StartLun = 13:00
EndLun = 13:30
EndTime = 17:30
If you do not want to have a lunch period, enter 13:00 into both the StarLun & EndLun cells so it will return a zero lunch period.

On your main page I would recommend using another column for the duration,
So use column “R” and add a header name of “Duration”, format the column as
:mm:ss
In cell “R2” use this formula and copy down.

Code: 
=IF(OR(A2="",I2=""),"-",IF(A2>I2,"Wrong Date",(NETWORKDAYS(A2,I2)-1)*(EndTime-StartTime+StartLun-EndLun)+IF(NETWORKDAYS(I2,I2),MEDIAN(MOD(I2,1), EndTime,StartTime)-MEDIAN(MOD(I2,1), EndLun,StartLun),EndTime-EndLun)-IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1), EndTime,StartTime)-MEDIAN(MOD(A2,1), EndLun,StartLun),StartTime-StartLun)))

In column “S” called “SLA Met / Not Met”, use this formula in cell “S2” and copy down.
Code: 
 =IF(R2<1,"Met","Not Met")

If you want to do the same thing for job completion column “K” use the same formula but adjust the formula and change all references from column ”I” to column “K”
This is assuming you wish to compare column “A” to column”K”.

Hope this helps
Paul.
Click to expand...




OK I have put it in put I am getting #VALUE!

Is it do with the time - I only have [h]:mm:ss and excell want allow me to put your time format in "
:mm:ss"



=IF(OR(A2="",I2=""),"-",IF(A2>I2,"Wrong Date",(NETWORKDAYS(A2,I2)-1)*(Availability!G47-Availability!G44+Availability!G45-Availability!G46)+IF(NETWORKDAYS(I2,I2),MEDIAN(MOD(I2,1), Availability!G47,Availability!G44)-MEDIAN(MOD(I2,1), Availability!G46,Availability!G45),Availability!G47-Availability!G46)-IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1), Availability!G46,Availability!G44)-MEDIAN(MOD(A2,1), Availability!G46,Availability!G45),Availability!G44-Availability!G46)))


thanks for you help
 
Upvote 0
Hi
Yeah, my bad, the correct format is [h]:mm:ss

I notice you are using cell references rather than the Named Range that I suggested, presumably Availability!G47 is the cell containing the End Time.
May I suggest you try it using the Named Ranges that I suggested.

regards
Paul.

 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Deleting multiple rows, columns, unmerging, pivot table all at once.
Replies
2
Views
102
HighAndWilder
H
L
  • Question Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
480
Lukma
L
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
527
XLfriend777
XLfriend777
G
  • Question Question
Filling cells with multiple " IF " & " THEN " in the formula
Replies
0
Views
272
Guy Boot
G
V
  • Solved
Find and replace.
2
Replies
12
Views
621
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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