Formula to skip holidays

[DaveAsh]

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.

 

[Bob Phillips]

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)

 

[Stan Brown]

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.

 

[DaveAsh]

=LOOKUP(J38,{1,2,3,4,5},E38+{"0.16666666666666666666666666666667","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]

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]

Yeah, sorry. I now have the following:

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

 

[David Biddulph]

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

Yeah, sorry. I now have the following:

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

....

 

[DaveAsh]

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]

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

 

[Bob Phillips]

So what is in D1:F5?

Based upon an assumption, how about

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