Vlookup to account for working hours mon-fri (9-5)
 

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 vlookup 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.083333333333333333 333333333333333","0.166666666666666666666666666666 67","1","2","7"})

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

Thanks.

Vlookup to account for working hours mon-fri (9-5)
 

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

--
HTH

Bob

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

"DaveAsh" wrote in message
...
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 vlookup 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.083333333333333333 333333333333333","0.166666666666666666666666666666 67","1","2","7"})

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

Thanks.

Vlookup to account for working hours mon-fri (9-5)
 

This does not work as it predicts times that are not working hours i.e. if
start date is 14/11/07 15:20 then a predicted end date is given as 14/11/07
17:20, please help:(

"Bob Phillips" wrote:

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

--
HTH

Bob

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

"DaveAsh" wrote in message
...
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 vlookup 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.083333333333333333 333333333333333","0.166666666666666666666666666666 67","1","2","7"})

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

Thanks.