Work out Service Level Agreement / Deadlines

Hello

I would really like some help trying to work out a formula that can calculate Service Level Agreement ("SLA") times for a workbook I am creating.

A Service Level Agreement is basically the agreed deadline to complete an action.

The SLA's I am working to are in hours but can be upto 240 Hours for certain types of actions.

The problem I am having is trying to work out the SLA time taking only into account working hours. I know there is a formula called Network Days or Hours or something but I would preferably like to omit this add-in if possible.

There are two sheets -

One sheet has all the action details and the SLA details for each action.
The other sheet has the agreed SLA times and working hours (e.g. Open till 8am-5pm Monday to Friday)

I have attached an example workbook to show how the data is set out.

If at all possible, could a formula be devised that can calculate the SLA Time for a given action, How long left until the action is out of SLA and a status of whether the SLA is still within or out of time.

Any assistance will be truly appreciated!

Thank you

Why don't you want to use the addin? It's very useful...

Hi Charlie

I would prefer not to have this add-in because people viewing the same workbook may not have the add-in enabled. However, if you don't think it's possible to do this calculation without the formula then I'll need to use the Network days. Would that be able to work out the time taking into account the opening hours?

Not on its own, but it does handle weekends and bank holidays much better than formulae can. Do you take an hour for lunch? I ask because if you do, then you can simply treat your SLA times as days (i.e. 8 hours = 1 working day if 1 hour for lunch).
This would make the solution much simpler...

CC

Unfortunately although I would have an hour lunch, there is always someone else to cover the hour so we would need to treat the working day as
8am-5pm.