I thought it worked okay as long as holidays don't have anything that falls
on Fri/Sat/Sun.

Check that. Sat/Sun are okay as Workday takes care of those. Perhaps
excluding the holidays that fall on Friday?

Just my 2 cents (overpriced perhaps - but since you can't cut a penny in two
I rounded up).



"JMB" wrote:

It looks like Roger's formula worked for w/o holidays, but got tripped up
because one of the holidays was on a Friday (which is skipped anyway).

I thought it worked okay as long as holidays don't have anything that falls
on Fri/Sat/Sun.

Or is there something I'm missing??

"T. Valko" wrote:

It still gets tripped up on different scenarios with or without accounting
for holidays.

Here's a nice test file if you're so inclined.

workday.xls 159kb

http://cjoint.com/?bkamHyKIF8

Input a start date, duration, add/remove holidays.

I still say this is complicated!

Biff

"Roger Govier" wrote in message
...
Hi Biff

I can see that you are correct.
My quick fix of increasing duration *5/4, doesn't account for completion
occurring on a Friday.
The following amendment fixes the problem (I think - not thoroughly
tested).

=IF(WEEKDAY(WORKDAY(Startdate,(Duration*5/4),holidays))=6,
WORKDAY(Startdate,(Duration*5/4+1),holidays),
WORKDAY(Startdate,(Duration*5/4),holidays)

--
Regards

Roger Govier


"T. Valko" wrote in message
...
Unless I'm missing something, I can't get Rogers or Martins formulas to
work.

Start date = 1/9/2007 (Tuesday Jan 9 2007)
Days = 11

Roger = 1/26/2007 (Friday Jan 26 2007)
Martin = 1/25/2007 (Thursday Jan 25 2007)

I believe the correct result should be 1/29/2007 (Monday Jan 29 2007)

I think this is much more complicated than it appears on the surface.
After about an hour of tinkering I haven't come up with anything that
works *under all circumstances*.

JMB's suggestion works but listing all Fridays as holidays may not be
very desirable.

I haven't tried Arvi's udf.

Biff

"Roger Govier" wrote in message
...
Thank you, Martin.
Much appreciated and Happy New Year.

--
Regards

Roger Govier


"Martin Fishlock" wrote in message
...
Roger,

That's a nice solution to the problem.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Roger Govier" wrote:

Hi

Provided you have ToolsAddinsAnalysis Toolpack selected, then you
could modify the Workday function.

=WORKDAY(Startdate,(Duration*5/4),holidays)
where holidays is a named range of holiday dates or a range of cells
e.g. $C$1:$C$9 containing the holiday dates.

Multiplying your duration by 5/4 will account for a 4 day week.

--
Regards

Roger Govier


"tamarade" wrote in message
...
I am trying to create a formula that will result in a date. I have a
start
date, a number of work days, however my dilemna is we only work 4
days. So
the formula would have to compute a completion date but exclude Fri,
sat, &
sunday, any suggestions?