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.083333333333333333 333333333333333","0.166666666666666666666666666666 67","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.

=IF(J38<3,IF(J38=1,E38+TIME(2,0,0)+(MOD(E38,1)=TI ME(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)

"DaveAsh" wrote in message
...
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.083333333333333333 333333333333333","0.166666
66666666666666666666666667","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.

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+VL OOKUP(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

On Dec 21, 3:16 pm, DaveAsh wrote:
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.083333333333333333 333333333333333","0.166666666666666666666666666666 67","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.

Thanks for the reply.

I have used the following formula that you gave:

=WORKDAY(E38,VLOOKUP(J38,$D$1:$F$5,2),AA1:AA34)+MO D(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.

"vezerid" wrote:

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+VL OOKUP(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

On Dec 21, 3:16 pm, DaveAsh wrote:
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.083333333333333333 333333333333333","0.166666666666666666666666666666 67","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.