Calculation of Breach

  • Afternoon all,


    I have attached a spreadsheet to hopefully make this explanation easier. I have two Sheets ('SLA's' & 'Calculations').


    In 'SLA's' this shows the 'Start' and 'Finish' time for each priority (I.e. P1's and 2's continue through the day/night, but P3's start at 08:00 and stop at 18:00) and shows their SLA's (2 hours, 4 hours and 8 hours).


    In the 'Calculations' sheet; what I would like to do is, based on the priority, calculate when the call will fail. So, for example, if a Priority 1 is raised on 21/09/2012 10:00, and it's SLA is 02:00 hours, it would fail at 21/09/2012 12:00. If a Priority 3 is raised on 21/09/2012 17:00, and it's SLA is 08:00 hours, it would fail on 22/09/2012 14:00.


    HELP!! :)

  • Re: Calculation of Breach


    No problem. It helps to know that Excel treats dates and times as a decimal. Dates themselves are whole numbers. Yesterday's date is represented by 41172, today is 41173. To add time, you add a fraction of the day, so today at noon is 41173.5. Today at 11:32 and 45 seconds (in the morning) is 41173.48108... Knowing that, if you want to add a day to a value, you add 1. To add one hour to a time, add 1/24. To add two hours, that's 2/24. Twelve hours is 12/24 (or 0.5).


    You should probably update your formula to this:


    =B10+IF(A10=1,2/24,IF(A10=2,4/24,8/24))+IF(AND(A10=3,OR(TEXT(B10,"H")*1>10,AND(TEXT(B10,"H")*1=10,LEFT(TEXT(B10,"mm:ss"),2)*1>0))),0.5,0)


    For this formula, we start with the leading value, or the time the ticket starts.
    =B2


    Next, we add two, four, or eight hours, depending on the priority. If A2 = 1, add 2/24 (or two hours). If A2 = 2, add 4/24 (four hours). Otherwise, it must be priority three, so add eight hours (8/24).
    +IF(A2=1,2/24,IF(A2=2,4/24,8/24))


    Last, if it's priority three, we need to add twelve hours, but only under certain circumstances. First, we check if it's priority three.
    +IF(AND(A2=3,


    Then, we check for the circumstances. We know priority three is only from 6 am to 6 pm, so we can do a test based on the hour of the day. If it comes in after ten in the morning, we have to add an extra twelve hours to our time. This is tricky, though. If it comes in exactly at ten am, there's no need to add half a day, but 10:01 does require the extra day. To solve for this, I use an OR statement.


    The first part of the OR checks to see if the hour is greater than ten. I do that by formatting the date/time value to just the hour. I used a text formula, which returns the text value of the number, so I multiply it by 1 to return it to a numeric value.
    OR(TEXT(B2,"H")*1>10,


    The second part of the OR checks to see if the value in the hours position is a ten and the value in the minutes position is greater than zero. Because Excel uses the text format "M" to represent the month, I had to figure out another way to get the minutes into my logic statement. That's why I asked you to update the formula. I had to pull out the minutes and seconds together ("mm:ss"), then take just the left two digits to extract the minutes. The old formula is adding half a day if the hour is ten and the month is greater than zero. We want to add half a day if the hour is ten and the number of minutes is greater than zero.
    AND(TEXT(B10,"H")*1=10,LEFT(TEXT(B10,"mm:ss"),2)*1>0))),


    Finally, if priority is three, and either the hour is greater than ten, or the hour IS ten and the minutes are greater than zero, what do we add? Half a day. Otherwise, we add zero.
    0.5,0)


    There's a lot going on there, but I've been playing with time calculations and service levels in Excel for ten years plus, so I feel pretty comfortable with this (and am a little embarrassed that I messed up with the first formula I gave you). Sorry about that - but this will get you where you need to go. If any of this is confusing, let me know and I'll take another pass at explaining it.

  • Re: Calculation of Breach


    Thank you jronaldlee :) It is going to take me a fair while to understand that, but thank you for explaining!!! I will get my head round it :-) While I am trying to get my head round it... Could you answer me a few questions?


    1) If I wanted to change the 'Work Hours' how do I do this? You reference above that "We know priority three is only from 6 am to 6 pm", how do I change this to be 8am to 6pm or 9am to 6pm? Is that easy to do or is that another mathematical/excel wizzardry task?


    2) If I wanted to add another priority, how do I do this? For example say I wanted to add another priority ('4') that has an SLA of 16 hours; how do I incoporate this? Is this also a wizzards task?


    I guess what I'm trying to gauge is if I need to make changes; if I can do it myself or if I'm going to have to shout for help everytime until I understand the explanation above!?

  • Re: Calculation of Breach


    1) It changes the formula, yes. If you were opening at 8 am instead of 6 am, you'd need to add 14 hours instead of 12. The part where you add 0.5 (twelve hours) would need to change to 14/24 (14 hours) if you open at 8 am, or 15/24 if you open at 9 am. The numerator is driven by how many hours from when you close to when you open.


    2) Priority 4 would complicate in two ways. First, the IF statements at the beginning of the formula would need another layer to account for the amount of time being added. Second, if you're saying the SLA is greater than the amount of business hours in the day, you're adding additional complexity. Consider the request that comes in at 5 p.m., it's not going to be due until the day after tomorrow. If you plan to go this route, I would simplify the whole thing by creating a more robust table with priorities and logic built in to determine how much extra pad you have to add to the new priority. You can then use VLOOKUPs to bring in the extra time by priority. Your formula would be much simpler; something like this:


    =B2+VLOOKUP(A2,SLAs!$A$1:$B$5,2,0)+IF(VLOOKUP(A2,SLAs!$A$1:$C$5,3,0)>TEXT(B2,"HH:MM"),0.5,0)+IF(VLOOKUP(A2,SLAs!$A$1:$D$5,3,0),0)>TEXT(B2,"HH:MM"),0.5,0)


    VLOOKUP 1: the amount of hours each priority has
    VLOOKUP 2: if the ticket is after the cut-off, add 12 hours so we know we can finish it the next day
    VLOOKUP 3: if the ticket is at a different time of day, we add another 12 hours (the 5:00 pm example with a 16 hour service level)

  • Re: Calculation of Breach


    Thank you :-) You are truly very intelligent, my mind is finding this hard to compute! Is the below an accurate reflection of what you have said?


    Quote from jronaldlee;629430

    1) It changes the formula, yes. If you were opening at 8 am instead of 6 am, you'd need to add 14 hours instead of 12. The part where you add 0.5 (twelve hours) would need to change to 14/24 (14 hours) if you open at 8 am, or 15/24 if you open at 9 am. The numerator is driven by how many hours from when you close to when you open.



    =B10+IF(A10=1,2/24,IF(A10=2,4/24,8/24))+IF(AND(A10=3,OR(TEXT(B10,"H")*1>10,AND(TEXT(B10,"H")*1=10,LEFT(TEXT(B10,"mm:ss"),2)*1>0))),0.58333,0)



    I think I will leave the Priority 4 for now :-) I don't want to confuse myself even more!!


    One further, hopefully quick, question... Are you able to change the close time? So the above formula is now 8am to 6pm (hopefully!) can I change it to 8am to 7pm? Sorry.. I ask so I can understand properly.

  • Re: Calculation of Breach


    If 8 am to 6 pm:


    =B10+IF(A10=1,2/24,IF(A10=2,4/24,8/24))+IF(AND(A10=3,OR(TEXT(B10,"H")*1>10,AND(TEXT(B10,"H")*1=10,LEFT(TEXT(B10,"mm:ss"),2)*1>0))),14/24,0)


    If 8 am to 7 pm:


    =B10+IF(A10=1,2/24,IF(A10=2,4/24,8/24))+IF(AND(A10=3,OR(TEXT(B10,"H")*1>11,AND(TEXT(B10,"H")*1=11,LEFT(TEXT(B10,"mm:ss"),2)*1>0))),13/24,0)


    I changed the hour marker in two if statements from 10 to 11 because 11 am is now eight hours prior to closing time.


    Add the following to stop weekend calculation:


    +IF(AND(A10=3,TEXT(B10,"DDD")="Fri"),2,0)


    All that does is add two days if the ticket is priority 3 and opened on a Friday.


    You're still going to have trouble with holidays in that scenario. In the past, I've solved for holidays by having a table of holidays, and using a VLOOKUP function to see how many days the office would be closed (a table of dates and how many extra days to add). If the open date is before a three day weekend, I add 1. If it's the day before Thanksgiving, add 2.

  • Re: Calculation of Breach


    I'm really sorrry.... I think i'm missing something!!


    For 8am to 6pm the end is

    Quote

    ),14/24,0)


    For 8am to 7pm the end us

    Quote

    ),13/24,0)


    I thought this section dictated what the start time was?


    Also, i'm really sorry but I don't understand this bit??


    Quote

    I changed the hour marker in two if statements from 10 to 11 because 11 am is now eight hours prior to closing time.