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.