Adding IF function to Lookup???

[DaveAsh]

Hi I have set up a lookup formula to calculate the projected finish time for
a job in the format dd/mm/yyyy hh:mm , this is:

=LOOKUP(J38,{1,2,3,4,5},E38+{"0.083333333333333333333333333333333","0.16666666666666666666666666666667","1","2","7"})

E38 is the date/time that the job was logged
J38 is the prority status (1-5)

The priority status related to:
1-completion should be within 2 working hours
2-" " " " " 4 working hours
3-" " " " " 1 day
4-" " " " " 2 days
5-contact should be within 2 days

How can i add to the formula using =if to make the formula not give days
which are weekends or out of hours (after 5pm to 8.59am)

Thanks.

 

[Bob Phillips]

=IF(J38<3,IF(J38=1,E38+TIME(2,0,0)+(MOD(E38,1)>=TIME(15,0,0))*TIME(16,0,0),E
38+TIME(4,0,0))+(MOD(E38,1)>=TIME(13,0,0))*TIME(16,0,0),WORKDAY(E38,LOOKUP(J
38,{3,4,5},{"1","2","7"})))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[vezerid]

The following formula is possibly more complex than needed but it
seems to work. For simplicity I used a group of cells in K25:M29:
1 0 =1/12
2 0 =1/6
3 1 0
4 2 0
5 7 0

As you can see the 2nd column contains the days and the 3rd column
contains the hours. With this in mind:

=WORKDAY(E38,VLOOKUP(J38,$K$25:$M$29,2),Holidays)
+MOD(E38,1)+VLOOKUP(J38,$K$25:$M$29,3)+(MOD(E38+VLOOKUP(J38,$K$25:$M
$29,3),1)>TIME(17,0,0))*2/3

You will need a range named Holidays as needed by WORKDAY.

HTH
Kostis Vezerides

 

[DaveAsh]

Thanks for the reply.

I have used the following formula that you gave:

=WORKDAY(E38,VLOOKUP(J38,$D$1:$F$5,2),AA1:AA34)+MOD(E38,1)+VLOOKUP(J38,$D$1:$F$5,3)+(MOD(E38+VLOOKUP(J38,$D$1:$F$5,3),1)>TIME(17,0,0))*2/3

(aa1:aa34 are weekends)

The formula almost works! I found one situation where the formula doesn't
work though. If the priority is 1 giving a job completion time of 4 hours and
the time is after 12:59pm on friday, then the formula gives a date/time on
the weekend.

How can i adjust the formula again!

Hope you can help.