Hi,
I have a holiday list set up, and a formula that works out the projected
completion date of a job in the format dd/mm/yyyy hh:mm. How can i add to the
formula so that if a day within the holiday list is predicted it skips to a
day after (which is not a holiday), and still gives an accurate date/time

e.g

Predicted time to complete is say 4 hours
start date/time: 28/12/2007 16:40
Finish time should be: 31/12/07 11:40

(The formula takes is to take into account working day (9-5 mon-fri)

Please help!

Thanks.

Show us your formula, and we might be able to help.

--
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 holiday list set up, and a formula that works out the projected
completion date of a job in the format dd/mm/yyyy hh:mm. How can i add to
the
formula so that if a day within the holiday list is predicted it skips to
a
day after (which is not a holiday), and still gives an accurate date/time

e.g

Predicted time to complete is say 4 hours
start date/time: 28/12/2007 16:40
Finish time should be: 31/12/07 11:40

(The formula takes is to take into account working day (9-5 mon-fri)

Please help!

Thanks.

=LOOKUP(J38,{1,2,3,4,5},E38+{"0.166666666666666666 66666666666667","1","2","7","14"})

J38 is the job priority (1-5)
1-Completion should be within 4 working hours
2-" " " " " 1 day
3-" " " " " 2 days
4-" " " " " 7 days
5-" " " " " 14 days
E38 is the job start date

I know the formula I am using is not the best but can i add to it a workday
and IF function? Or is there a better way to do this?

Thanks.
"Bob Phillips" wrote:

Show us your formula, and we might be able to help.

--
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 holiday list set up, and a formula that works out the projected
completion date of a job in the format dd/mm/yyyy hh:mm. How can i add to
the
formula so that if a day within the holiday list is predicted it skips to
a
day after (which is not a holiday), and still gives an accurate date/time

e.g

Predicted time to complete is say 4 hours
start date/time: 28/12/2007 16:40
Finish time should be: 31/12/07 11:40

(The formula takes is to take into account working day (9-5 mon-fri)

Please help!

Thanks.

Didn't I give you this before, where I used the WORKDAY function embedded in
part of what you have?

--
HTH

Bob

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

"DaveAsh" wrote in message
...
=LOOKUP(J38,{1,2,3,4,5},E38+{"0.166666666666666666 66666666666667","1","2","7","14"})

J38 is the job priority (1-5)
1-Completion should be within 4 working hours
2-" " " " " 1 day
3-" " " " " 2 days
4-" " " " " 7 days
5-" " " " " 14 days
E38 is the job start date

I know the formula I am using is not the best but can i add to it a
workday
and IF function? Or is there a better way to do this?

Thanks.
"Bob Phillips" wrote:

Show us your formula, and we might be able to help.

--
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 holiday list set up, and a formula that works out the
projected
completion date of a job in the format dd/mm/yyyy hh:mm. How can i add
to
the
formula so that if a day within the holiday list is predicted it skips
to
a
day after (which is not a holiday), and still gives an accurate
date/time

e.g

Predicted time to complete is say 4 hours
start date/time: 28/12/2007 16:40
Finish time should be: 31/12/07 11:40

(The formula takes is to take into account working day (9-5 mon-fri)

Please help!

Thanks.

Yeah, sorry. I now have the following:

=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 a list of weekends)

The only problem that i have with this though is that when the priority is 1
(4 hours projected time) and the start date/time is after 1pm on a Friday,
the formula still gives weekends as a projected finish date.

How can i adjust the formula to take this into account?

"Bob Phillips" wrote:

Didn't I give you this before, where I used the WORKDAY function embedded in
part of what you have?

--
HTH

Bob

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

"DaveAsh" wrote in message
...
=LOOKUP(J38,{1,2,3,4,5},E38+{"0.166666666666666666 66666666666667","1","2","7","14"})

J38 is the job priority (1-5)
1-Completion should be within 4 working hours
2-" " " " " 1 day
3-" " " " " 2 days
4-" " " " " 7 days
5-" " " " " 14 days
E38 is the job start date

I know the formula I am using is not the best but can i add to it a
workday
and IF function? Or is there a better way to do this?

Thanks.
"Bob Phillips" wrote:

Show us your formula, and we might be able to help.

--
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 holiday list set up, and a formula that works out the
projected
completion date of a job in the format dd/mm/yyyy hh:mm. How can i add
to
the
formula so that if a day within the holiday list is predicted it skips
to
a
day after (which is not a holiday), and still gives an accurate
date/time

e.g

Predicted time to complete is say 4 hours
start date/time: 28/12/2007 16:40
Finish time should be: 31/12/07 11:40

(The formula takes is to take into account working day (9-5 mon-fri)

Please help!

Thanks.

WORKDAY shouldn't need a list of weekends. The second (optional) parameter
is a list of holidays. Weekends get omitted anyway.
--
David Biddulph

"DaveAsh" wrote in message
...
Yeah, sorry. I now have the following:

=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 a list of weekends)
....

So what is in D1:F5?

Based upon an assumption, how about

=((WORKDAY(E38,VLOOKUP(J38,$D$1:$F$5,2))+MOD(E38,1 ))*(J381))+
((WORKDAY(E38,1)+MOD(E38,1)-TIME(4,0,0))*(MOD(E38,1)TIME(13,0,0))*(J38=1))+
((E38+TIME(4,0,0))*(MOD(E38,1)<=TIME(13,0,0))*(J38 =1))

--
HTH

Bob

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

"DaveAsh" wrote in message
...
Yeah, sorry. I now have the following:

=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 a list of weekends)

The only problem that i have with this though is that when the priority is
1
(4 hours projected time) and the start date/time is after 1pm on a Friday,
the formula still gives weekends as a projected finish date.

How can i adjust the formula to take this into account?

"Bob Phillips" wrote:

Didn't I give you this before, where I used the WORKDAY function embedded
in
part of what you have?

--
HTH

Bob

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

"DaveAsh" wrote in message
...
=LOOKUP(J38,{1,2,3,4,5},E38+{"0.166666666666666666 66666666666667","1","2","7","14"})

J38 is the job priority (1-5)
1-Completion should be within 4 working hours
2-" " " " " 1 day
3-" " " " " 2 days
4-" " " " " 7 days
5-" " " " " 14 days
E38 is the job start date

I know the formula I am using is not the best but can i add to it a
workday
and IF function? Or is there a better way to do this?

Thanks.
"Bob Phillips" wrote:

Show us your formula, and we might be able to help.

--
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 holiday list set up, and a formula that works out the
projected
completion date of a job in the format dd/mm/yyyy hh:mm. How can i
add
to
the
formula so that if a day within the holiday list is predicted it
skips
to
a
day after (which is not a holiday), and still gives an accurate
date/time

e.g

Predicted time to complete is say 4 hours
start date/time: 28/12/2007 16:40
Finish time should be: 31/12/07 11:40

(The formula takes is to take into account working day (9-5 mon-fri)

Please help!

Thanks.

Thu, 27 Dec 2007 01:35:00 -0800 from DaveAsh
:
I have a holiday list set up, and a formula that works out the projected
completion date of a job in the format dd/mm/yyyy hh:mm. How can i add to the
formula so that if a day within the holiday list is predicted it skips to a
day after (which is not a holiday), and still gives an accurate date/time

See the WORKDAY() function. I believe you must have Analysis Toolpak
installed.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/