How to calculate incident SLA expiration

[Puni]

Hi,

I work on a helpdesk and need to calculate when an incident SLA is due to expire, relative to the hours of contracted service. We provide service 0700 to 2300hrs 7 days a week, and are required to fix a fault within 85 minutes, I can add 85 minutes to an incident start time when the start time is well within the hours of service, however, am coming unstuck if the incident is created at say, 2230hrs. How do I calculate 30mins up to 2300hrs and then another apply the remaining 55 minutes from 0700hrs to show the SLA expires at 0755hrs?

Thanks for your help

[jason.b75]

Applying simple logic, if the start time is after 21:35 (85 minutes before 23:00) then add 8 hours (difference between 23:00 and 07:00) to the 85 minutes allowed.

In terms of an excel formula, with start time in A1

=IF(A1>(--"21:35"),A1+"09:25",A1+"01:25")

[Puni]

Thanks Jason,

How would you set up the formula for start times prior to 21:35? We have 100's of incidents that can have a date with a start time between 0700 and 2300hrs so looking for a formula I can apply to every row etc.

Thanks,

[jason.b75]

The sample formula takes care of that, the end part, A1+"01:25" adds 85 minutes to times not later than 21:35.

The only thing that I didn't allow for, was a start time between 23:00 and 07:00, i.e. starting out of hours. I assumed not necessary based on the information in your post, but it can be allowed for if needed.

[Puni]

Brilliant, thank you!

No, there would be no start times between 2300 and 0700hrs so will work well

Cheers