SLA Calulation excluding weekends

B

big40ish

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

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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

L
  • Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
227
Lukma
L
K
  • 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
149
XLfriend777
XLfriend777
V
  • Solved
Find and replace.
2
Replies
12
Views
443
NdNoviceHlp
NdNoviceHlp
S
  • Solved
Need help modifying an Excel VBA coded to add a check for new hires less that 90 days.
Replies
1
Views
379
spittingfire
S
A
  • Question
Calculate the average of a column with time excluding rows with values in other columns
Replies
4
Views
495
arthurbr
arthurbr

Forum statistics

Threads
1,214,644
Messages
6,120,709
Members
448,983
Latest member
Joaquim_Baptista

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