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]

OK got it, bit trickier!

 

[Legacy 320862]

Thought it may be! It's not a problem if this will take too much time to sort or if just too much. I think I can definitely work with what we have already, it works much better now with the benefit of your expert help. Thank you

 

[gaz_chops]

Just managed to look at this, do you still need it?

 

[Legacy 320862]

Hi Gaz, yes please if you've cracked it that would be great! Thank you

 

[gaz_chops]

Hi Jenny,
Enter in E11 "Target Start Date", the "Target Completion Date" should be ok as it is, once this one is entered. Providing it works as you expect!
test with various dates & Priorities.

=IF(AND($D11<3,HOUR(MOD($C11,1)+INDEX($G$4:$G$8,MATCH($D11,$B$4:$B$8,0)))>=17),WORKDAY($C11,1)+MOD($C11+(9/24-(MOD($C11,1)))-("17:00"-MOD($C11,1))+INDEX($G$4:$G$8,MATCH($D11,$B$4:$B$8,0)),1),IF($D11=3,WORKDAY($C11,1)+MOD($C11+(9/24-(MOD($C11,1)))-("17:00"-MOD($C11,1))+INDEX($G$4:$G$8,MATCH($D11,$B$4:$B$8,0)),1),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)))

Gaz

 

[Legacy 320862]

Hi Gaz,

Genius! It works perfectly. Thank you for your time and help on this. It's spot on!

 

[gaz_chops]

You're welcome, note if the priority times change it may need tweaking!