Conditional Formatting

[DaveAsh]

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. I am trying to make this function automated to give the predicted
finish date when any one of the 1-5 are chosen.

1 prority takes 2 hours
2 4 hours
3 1 day
4 2 days

How can i achieve this function?

 

[Stephen]

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. I am trying to make this function automated to give the
predicted
finish date when any one of the 1-5 are chosen.

1 prority takes 2 hours
2 4 hours
3 1 day
4 2 days

How can i achieve this function?

You make a small table somewhere (say, X100:Y105) with your priorities in
the first column and the corresponding times in the second. Then you use a
VLOOKUP formula to look up the priority in this table and return the
corresponding time. For example,
=VLOOKUP(A1,X100:Y104,2,FALSE)

 

[Gord Dibben]

=LOOKUP(B1,{1,2,3,4,5},{"2 hours","4 hours","1 day","2 days","1 week"})

Assuming B1 is the dropdown cell.

You mentioned 1-5 so I added the 1 week, adjust to suit.


Gord Dibben MS Excel MVP

 

[DaveAsh]

Thanks for that, however the formula just gives "2 hours" or "4 hours" etc. I
have a start date column set up in the format dd/mm/yyyy hh/mm and i want the
formula to add say "2hours" or "4hours", "1 week" etc. to this time/date. How
can i change the formula to give this new predicted job finish time/date?

 

[DaveAsh]

I also need the formula to recognise working hours when computing the
time/date i.e. acknowledge 9-5 day, mon-fri.