Formula to skip holidays
 

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.

Formula to skip holidays
 

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.

Formula to skip holidays
 

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/

Formula to skip holidays
 

=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.

Formula to skip holidays
 

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.

Formula to skip holidays
 

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.

Formula to skip holidays
 

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)
....

Formula to skip holidays
 

I know it should omit weekends, but for some reason the formula i am using
does not even with the add-in. Maybe there is an error within the formula
that is stopping this?

"David Biddulph" wrote:

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)
....

Formula to skip holidays
 

You've added lots of extra terms after the WORKDAY function. Have you
checked that WORKDAY is doing the wrong thing in isolation? It's always a
good idea to break a long formula into manageable chunks if you need to
debug it.

I don't know quite how your formula is intended to work, but I would have
thought that most of the terms would need to be inside a WORKDAY function to
get the result on the right day. If you are adding an extra term after your
first WORKDAY function and that extra term might take it from one day to
another, then you'll probably need another WORKDAY function to allow that to
step over the weekend.

If your extra terms might give a number of whole days + a time of day for
the finishing point, then perhaps
=WORKDAY(WORKDAY(... first addition ...),INT(extra term))+MOD(extra term,1)
?
--
David Biddulph

"DaveAsh" wrote in message
...
I know it should omit weekends, but for some reason the formula i am using
does not even with the add-in. Maybe there is an error within the formula
that is stopping this?

"David Biddulph" wrote:

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)
....

Formula to skip holidays
 

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.