Lookup working days

[Guest]

Hi,
I have a column set up with a drop down menu listing the priority of a
job(1-5). I have another column set up which gives the predicted finish date
of the job as dd/mm/yyyy hh/mm. I have made a function with lookup that
gives the predicted finish date when any one of the 1-5 are chosen.

1 priority takes 2 hours
2 4 hours
3 1 day
4 2 days
5 1 week

This is the formula i have used:
=LOOKUP(J38,{1,2,3,4,5},E38+{"0.083333333333333333333333333333333","0.16666666666666666666666666666667","1","2","7"})

How can i adjust it to take into account working days (mon-fri 9am-5pm)

Thanks.

 

[Bob Phillips]

=IF(J38>2,WORKDAY(E38,LOOKUP(J38,{3,4,5},{"1","2","7"})),E38)+(TIME(2,0,0)*(J38=1))+(TIME(2,0,0)*(J38=2)*2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

This still allows for times which are out of hours, i.e. still predicting
times between 5pm and 9am