Excel Grade of Service / SLA formulas

[totalmat]

Apologies I'm very very new to excel formulas etc. so please be patient with me </SPAN></SPAN>

I have a spreadsheet which contains raw message stats and need to be able to work out if each message received is within 1 hour SLA however I'm not sure the best way to do this or easiest. In addition to this, I need to only calculate and factor in working days and time.

Below is the data table I have (as described above as raw message stats). You'll see that the first two entries were resolved in well under 1 hour and within our working day and time so not a problem however the third would fail as it was received out of hours so if I need a way to factor out, out of hours time (as GOS for me is based on working days and hours).

My working hours are Monday to Friday 08:00am until 18:00pm.

Any help anyone could give would be massively appreciated.
[TABLE="class: grid, width: 30"]
<TBODY>[TR]
[TD]Received
[/TD]
[TD]Closed
[/TD]
[TD]Resolution Time (Secs)
[/TD]
[/TR]
[TR]
[TD]30/12/2013 17:06:40
[/TD]
[TD]30/12/2013 17:08:45
[/TD]
[TD]125
[/TD]
[/TR]
[TR]
[TD]30/12/2013 16:59:21
[/TD]
[TD]30/12/2013 17:03:28
[/TD]
[TD]247
[/TD]
[/TR]
[TR]
[TD]30/12/2013 00:04:02
[/TD]
[TD]30/12/2013 08:17:17
[/TD]
[TD]29595
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Pup Denab]

HI totalmat
Welcome to the forum

Place this formula in cell D2, it will calculate the minutes and change the out of hours calls to 8am received, then calculate the time spent i.e. your example 17 mins

=B2-IF(OR(HOUR(A2)<8,HOUR(A2)>17),"08:00:00",A2)
You will also need to format the cell D2 as custom format hh:mm:ss

You will also probably need to extend the formula, to take days into account, if it takes more than 1 working day to resolve, I'll assume it late ? like below
=IF(NETWORKDAYS(A2,B2)>1,"Late",B2-IF(OR(HOUR(A2)<8,HOUR(A2)>17),"08:00:00",A2))

 

[totalmat]

Great thanks Pup, really helpful and so easy even I could do it!

The first formula works best as the second one you provided places 'Late' in the result if within the range of received and closed is over a weekend and I need this to remain a number if that makes sense.

Really appreciate your help, should I need to ask another question about this I'll add another post on this thread.

 

[totalmat]

Hi again Pup

I've just realised through testing some scenarios that you're right, I will need to use the second formula you have provided as some received are not closed the same day however I dont want it to return 'Late' I want it to return the resolution time (excluding non working days and hours) Here's an example

Received - 24/12/2013 08:00
Closed - 31/12/2013 08:00
Desired calculation result: 50:00:00 (based on working hours being 08:00am until 18:00pm, Monday to Friday)

Any ideas?

Thanks in advance

 

[Pup Denab]

HI totalmat
Again it looks easy & it hurt my head, but the new formula, will do as you require, you can custom format to general format this time, as the formula, will do the formatting for you, please check and get back to us, if its still not right, (probably need a larger data set, if you run into problem)

I used cell D2 you can moved to C2 if you need to, but the cell references must match the row their in ok

=TEXT(IF(TEXT(B2,"hh:mm:ss")>IF(OR(TEXT(A2,"hh:mm:ss")<"08:00:00",TEXT(A2,"hh:mm:ss")>"18:00:00"),"08:00:00",TEXT(A2,"hh:mm:ss")),TEXT(B2,"hh:mm:ss")-IF(OR(TEXT(A2,"hh:mm:ss")<"08:00:00",TEXT(A2,"hh:mm:ss")>"18:00:00"),"08:00:00",TEXT(A2,"hh:mm:ss"))+(IF(NETWORKDAYS(A2,B2)-1<0,0,NETWORKDAYS(A2,B2)-1)*"10:00:00"),SUM("18:00:00"-IF(OR(TEXT(A2,"hh:mm:ss")<"08:00:00",TEXT(A2,"hh:mm:ss")>"18:00:00"),"08:00:00",TEXT(A2,"hh:mm:ss")),TEXT(B2,"hh:mm:ss")-"08:00:00",((IF(NETWORKDAYS(A2,B2)-1<0,0,NETWORKDAYS(A2,B2)-1)-1)*"10:00:00"))),"[hh]:mm:ss")

 

[totalmat]

Ideal thank you, this looks to have done the job perfectly.

How do I now do a count for all cells in rande D2:D600 where the result is 01:00:00 or less?

 

[totalmat]

Pup - scrap my last entry (today at 11:24), I have worked out a way of counting the entries now by converting the results into seconds in another cell and doing it this way.

The only thing I am now stuck on is the earlier formula

=TEXT(IF(TEXT(B2,"hh:mm:ss")>IF(OR(TEXT(A2,"hh:mm:ss")<"08:00:00",TEXT(A2,"hh:mm:ss")>"18:00:00"),"08:00:00",TEXT(A2,"hh:mm:ss")),TEXT(B2,"hh:mm:ss")-IF(OR(TEXT(A2,"hh:mm:ss")<"08:00:00",TEXT(A2,"hh:mm:ss")>"18:00:00"),"08:00:00",TEXT(A2,"hh:mm:ss"))+(IF(NETWORKDAYS(A2,B2)-1<0,0,NETWORKDAYS(A2,B2)-1)*"10:00:00"),SUM("18:00:00"-IF(OR(TEXT(A2,"hh:mm:ss")<"08:00:00",TEXT(A2,"hh:mm:ss")>"18:00:00"),"08:00:00",TEXT(A2,"hh:mm:ss")),TEXT(B2,"hh:mm:ss")-"08:00:00",((IF(NETWORKDAYS(A2,B2)-1<0,0,NETWORKDAYS(A2,B2)-1)-1)*"10:00:00"))),"[hh]:mm:ss")

I have now tested this on a larger data set and for some reason 50% return #VALUE!

I think this is because some of the received dates are weekends or out of hours, could I be right?

Happy to email you thr file so you can see yourself if thats easier? If so private/direct message me your email address or I can send you mine.

 

[Pup Denab]

Hi
I have updated the formula to work correctly

=TEXT(IF(TEXT(B2,"hh:mm:ss")<"08:00:00","08:00:00",TEXT(B2,"hh:mm:ss"))-TEXT(IF(OR(LEFT(TEXT(WEEKDAY(A2),"ddd"),1)="s",TEXT(A2,"hh:mm:ss")<"08:00:00",TEXT(A2,"hh:mm:ss")>"18:00:00"),"08:00:00",TEXT(A2,"hh:mm:ss")),"hh:mm:ss")+TEXT(NETWORKDAYS(A2,B2)-1,"[h]:mm:ss")*("10:00:00"),"[hh]:mm:ss")

 

[tdimblebee]

Hi,

I'm new to this forum and have spent hours searching the internet for the function above for my SLA reporting and wanted to say a huge thanks to Pup.

I really don't know why there's not a free template of this nature that people can amend for there business hours etc or even an app for mobile devices given the need to report on performance, suppliers etc.

I do however need one last thing for my report, can someone give me the function to calculate the Total SLA (I2) which takes the suspended hours (H2) from Start/End Hrs (E2)?

C2 D2 E2 F2 G2 H2 I2

[TABLE="width: 672"]
<tbody>[TR]
[TD]SLA "Start"
[/TD]
[TD]SLA "End"
[/TD]
[TD]Start/End Hrs
[/TD]
[TD]Hold
[/TD]
[TD]Release
[/TD]
[TD]Suspended
[/TD]
[TD]Total SLA
[/TD]
[/TR]
[TR]
[TD]05/04/2010 08:00
[/TD]
[TD]09/04/2010 08:00
[/TD]
[TD]40:00:00
[/TD]
[TD]07/04/2010 10:00
[/TD]
[TD]09/04/2010 09:00
[/TD]
[TD]19:00:00
[/TD]
[TD]?????
[/TD]
[/TR]
</tbody>[/TABLE]


Many thanks
Trace