Calculating Networkdays Between Two Dates (Factoring in Work Received Outside of Business Hours) for SLA

[Funkybot]

Hi, I'm having trouble coming up with a formula (or formulas) needed as part of an SLA calculation.

A1: Case Received Date and Time (in MM/DD/YYYY hh:mm AM/PM format)
B1: Case Completed Date and Time (same format)

I need to compare these two dates to identify if the case was completed within the SLA. The SLA rules are: 1) cases received on a workday before 5PM must be responded by 5PM the next workday, and 2) if a case received outside of business hours, it will be considered as having been received during the next workday.

Example 1: a Case Received Friday at 4:58PM must be completed by Monday at 5PM. If yes, it "Made Service," if completed after Monday at 5PM, it "Missed Service."
Example 2: a Case Received Friday at 5:01PM (or Saturday or Sunda) must be completed by Tuesday at 5PM.

I want to use the NetWorkDays function so I can exclude holidays.

So far, I've split out the Date and Time into different columns, and used the Net Work Day function to calculate business days between the start and end date, then an "IF" statement combined with the TIME function to determine if an email was completed before or after 5PM. Where I've gotten stuck is how to tell Excel that a case received after 5PM should count towards the next day. Hoping someone here can help.

 

[Tetra201]

Try this:

=IF(WORKDAY(WORKDAY(A1+"6:59:59"-1,1),1)+"17:00" < B1,"Missed Service","Made Service")

 

[Funkybot]

Try this:

=IF(WORKDAY(WORKDAY(A1+"6:59:59"-1,1),1)+"17:00" < B1,"Missed Service","Made Service")

Thanks! That seems to do it. I just tweaked the start time to 7:59;59 and added in the holidays (in cells H2;H11). The end result looked like this and seems to work perfectly so far:

=IF(WORKDAY(WORKDAY(A1+"7:59:59"-1,1,$h$2:$h$11),1,$h$2:$h$11)+"17:00" < B1,"Missed Service","Made Service")