SLA - Based on varying priority, add time to a date excluding non working hours

[Legacy 320862]

Hi

If a job is logged at 13/03/2015 16:00:00 (a Friday), and is priority 1, then the job must be completed within 24 working hours.
If a job is logged at 13/03/2015 16:00:00 (a Friday), and is priority 2, then the job must be completed within 48 working hours.
(there are 5 priority levels)

I currently have the 'deadline' cell populated with this formula to work out, based on priority, how much time to add on to create the deadline, however, how would I exclude non working hours? :

=IF($D11=1, $C11+$K$8, IF($D11=2, C11+$K$7, IF($D11=3, C11+$K$6, IF($D11=4, C11+$K$5, IF($D11=5, C11+$K$4, " ")))))

D11 is the priority of the task (1,2,3,4 or 5)
K8 - K4 are the different times allowed to complete the task.
C11 is the time the job is logged.

I can send a spread sheet if this helps to explain?

Thank you so much for your help!
Jen

 

[gaz_chops]

Ah you're using the Target Start Date

=WORKDAY(E11,INDEX($K$4:$K$8,MATCH(D11,$B$4:$B$8,0)))+MOD(E11,1)

 

[Legacy 320862]

That's done the trick! Perfect!
Thank you so much for all your help this morning. Have a lovely day!

 

[gaz_chops]

You're welcome, you too.

 

[Legacy 320862]

I'm So sorry I'm back. I've just tried the same thing for Column E, this works out the time until a response must be given, and the values are located in column G. I thought it would be exactly the same, just switch the K's to G's. It works for priority 4&5 as these are more than 24 hours, but not the first 3 priorities which are less than 24 hours. Please could you help me again? How would I have it add on 20 minutes for priority 1? 2 hours for priority 2 and 8 hours for priority 3? Working hours are 9-5, is there any way to factor in this too please? Thank you

 

[gaz_chops]

No prob, try

=WORKDAY(C11,INDEX($G$4:$G$8,MATCH($D11,$B$4:$B$8,0)))+MOD(INDEX($G$4:$G$8,MATCH($D11,$B$4:$B$8,0)),1)+MOD(C11,1)

 

[Legacy 320862]

Thank you that does work perfectly, however, is there any way to factor in the working hours 9-5? Eg if a priority 3 job is logged at 5pm, the 8 hours later deadline should be 5pm the next day and not 1am the next day? I'm not sure if this is possible? Thank you again

 

[gaz_chops]

Wouldn't it be 9am the next day?

 

[Legacy 320862]

If a job was logged at 5pm then 8 working hours later would work out at 5pm the next day. The count would start at 9am and they have 8 hours to respond so the deadline would be 5pm the next day.

 

[gaz_chops]

I was thinking of the Target Start time as well, so if reported at 5pm the Target Start would be 9am the next morning, is that what you expect?

 

[Legacy 320862]

The target start time is what I would like to have a formula for. So this formula would look at the Report date & time (C11), then based on the priority will add one of G4:G9.

So if reported at "24/03/2015 17:00:00", priority 3 (G6 - 8hrs) then the target start date (E11) should display 25/03/2015 17:00:00.

If reported at "24/03/2015 16:00", priority 2 (G7 - 2 hours) then the target start date would be 25/03/2015 10:00.

Does that make sense?