SLA Calulation excluding weekends

big40ish

New Member
Joined
Nov 10, 2018
Messages
3
Reported DateDay of the WeekSDT NumberMachine TypeSerial NumberReported FaultStatusAction TakenCall Out Response DateCall Out Response TimeJob Completion Date/TimeMachine Down TimeEngineerAreaReason for unit still OOSAdditional infoSLASLA MET/NOT MET
21/10/2018 22:43SundaySDT23578Ionscan 400B31207Unable to verifyNot completeVerific but wont print out22/10/2018 15:3016:47:0031/10/2018 23:59241:16:59Neil RedmondT2 lane 8Parts requiredcomms board 0
22/10/2018 03:56MondaySDT23641Ionscan 400B12513Unable to verifyNot completeSlide tray damaged22/10/2018 16:0012:04:0031/10/2018 23:59236:03:59Neil RedmondT2 StaffParts requiredNew slide tray0
22/10/2018 13:49MondaySDT23646Ionscan 400B12515Unable to verifyCompleteSample inlet and APT reset23/10/2018 11:4521:56:0023/10/2018 12:1422:25:00Neil RedmondT1 Lane 8N/ABIS1
23/10/2018 07:36TuesdaySDT23659Ionscan 400B25609Unable to verifyCompleteSample inlet and APT reset23/10/2018 12:304:54:0023/10/2018 12:455:09:00Neil RedmondT1 Bravo Lane 18N/ABIS1
22/10/2018 23:48MondaySDT23658Ionscan 400B33098Unable to verifyCompleteSample inlet and APT reset23/10/2018 15:0015:12:0023/10/2018 15:2015:32:00Neil RedmondT2 Lane 8N/ABIS1
30/10/2018 00:05TuesdaySDT23679Ionscan 400B33098Calibrant not stableCompleteAPT reset & Ref peak adjust30/10/2018 16:1516:10:0030/10/2018 17:1517:10:00Neil RedmondT2 Lane 6N/ABIS1
31/10/2018 10:54WednesdaySDT23730Ionscan 400B31207Loose slide tray assemblyCompleteRefitted slide tray assembly31/10/2018 12:001:06:0031/10/2018 12:301:36:00Neil RedmondT2 Lane 6N/ABIS1
26/10/2018 22:47FridaySDT23695Ionscan 400B25609Unable to verifyCompleteSample inlet and APT reset31/10/2018 14:15111:28:0031/10/2018 14:45111:58:00Neil RedmondT1 Bravo Lane 18N/ABIS4
30/10/2018 05:17TuesdaySDT23720Ionscan 400B32549False activationsCompleteClean and condenser filter02/11/2018 09:0075:43:0002/11/2018 10:3077:13:00Neil RedmondT3 Service yardN/ABIS4

<colgroup><col span="2"><col><col span="2"><col><col><col span="11"></colgroup><tbody>
</tbody>


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

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!!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
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).
 

big40ish

New Member
Joined
Nov 10, 2018
Messages
3
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
 

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
752
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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.
 

big40ish

New Member
Joined
Nov 10, 2018
Messages
3
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.





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
 

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
752
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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.

 

Watch MrExcel Video

Forum statistics

Threads
1,128,176
Messages
5,629,176
Members
416,370
Latest member
Lgathana

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
Top