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

[jenny00001]

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]

What are the values in K4-K8

 

[jenny00001]

K8 is 08:00:00 (8 working hours, 24 actual hours)
K7 is 16:00:00 (16 working hours, 48 actual hours)
K6 is 24:00:00 (24 working hours, 72 actual hours)
K5 is 32:00:00 (32 working hours, 96 actual hours)
K4 is 32:00:00 (32 working hours, 96 actual hours)

These are the times allowed based on the priority of the task. Priority 1 (K8) must be completed within 8 working hours. (apologies, my original message said 24 working hours by mistake, this should have been just 24 hours, which is 8 working hours)

I hope this helps to explain.
Thank you

 

[gaz_chops]

Try this, J4-K8 is a small table with Priority No. & Times

=WORKDAY(C11,VLOOKUP(D11,$J$4:$K$8,2)/24)

 

[jenny00001]

Thanks for your help, unfortunately I am getting #N/A with this formula. I there a way I could send you my spread sheet?

I think the problem is that K4-K8 are in this format - "01/01/1900 00:00:00" - custom [h]:mm:ss and I don't know how to get this to display 8,16 hours etc. I can only get it to show 24, 48 etc hours.

 

[gaz_chops]

You can upload the file to Dropbox, then post the link here.

 

[jenny00001]

https://www.dropbox.com/s/g0xmoionrgoshdm/excel example.xlsm?dl=0

Thank you so much, you will see it's slightly different to as described, I was trying to simplify the question when explaining in the first instance. I hope this makes sense.

 

[gaz_chops]

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

 

[jenny00001]

Brilliant, I think we're very nearly there. I've put that formula into G11, but the display is showing 16/03/2015 00:00 instead of 16/03/2015 10:20, do you know how I would get the times to display correctly please?

I can't thank you enough for your help on this, you are a genius! thanks

 

[gaz_chops]

This one is better, picks up the time aswell

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

You will need to change the Rating to JUST
5
4
3
2
1