SLA Counter Formual based on operation hours

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

Thank you cobwebs.

I will give it a try shortly get back to you

Hi Cobwebs I cant seem to get to it work correctly. I recheck to ensure the COL's are correct in the my spreasheet.

Hi mate, really sorry but i messed up my criteria my business hours is M - F 07:00 - 21:00 and Sat - Sun 07:00 - 17:30. Really sorry mate to have wasted your time I stuff up my own business hours when trying to explain.

Hi cobwebs,

Are you able to assist here my business hour criteria has changed:

Criteria outlined below:
1. Monday to Friday (07:00-21:00)
2. Saturday to Sunday (07:00-17:30)

Can you formula be changed easily? And also I was thinking if an email is sent after hours however if a staff is working late night and decides to respond to the vendor how can this be marked as a pass?

Thank you

Ravi