Hi team,

I am really stuck with a situation at hand. I am creating a SLA counter which will derive 1 or 0. My SLA clock revolves around hours of our business which is from Monday to Sun 7AM - 21:00. At 21:00 every day our business closes and opens up again at 7AM next day.

My Outcome:
During our hours of operation we receive emails from our supplier and we have a SLA to respond within 2hours. If we respond within the agreed SLA then I manually mark a “1” in the spreadsheet and if we don’t meet this SLA then I mark a “0”. However if the email is sent after hours then our SLA clock doesn’t start till the next day at 7AM. Now I am stuck as I don’t have the knowledge to figure this out and the complication of hours involved.

Example Attached:
COL A & B = Supplier record of when the email was sent to the business
COL C & D = Business record of when we have responded to the email back to the supplier.
COL E & F = calculates the HRS & DURATION
COL G = SLA count of 2 hours. 1 or 0 (pass/fail)

Row 5 highlighted in orange where is shows business responded in 11:34 (11hrs & 34mins). However we have met the SLA as the supplier had sent the email at 2100 outside of business hours. So our 2hr SLA wont start till the next day at 07:00. We had responded back to the supplier at 08:34 meeting our SLA hence I have manually marked a 1 in the SLA COL.

Now this is where it is getting complicated for me. I am unable to work out the duration in CoL E to show 1:34 instead it show 11:34. If someone can help me with a formula which I would love to learn will be greatly appreciated.

Thanks
RPC06