Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
working with dates and hours | Excel Worksheet Functions | |||
working hours | Excel Worksheet Functions | |||
Working hours | Excel Discussion (Misc queries) | |||
Negative Working Hours | Excel Worksheet Functions | |||
Calculating working hours | Excel Discussion (Misc queries) |