there is no "typo" in Chip's response, I'm assuming you're refering to the
11, which is a new possibility for the WEEKDAY ReturnType in Excel 2010.

Yes, that would be a problem in versions prior to 2010. I should have
made that clear. For earlier versions, use the following:

=WORKDAY(A1+100,--(WEEKDAY(A1+100, 2)5),Holidays)

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Thu, 6 May 2010 08:30:08 +0100, "Steve Dunn" wrote:

Hi Mike,

there is no "typo" in Chip's response, I'm assuming you're refering to the
11, which is a new possibility for the WEEKDAY ReturnType in Excel 2010. I
suspect Chip didn't mention it was 2010 only for the same reason that I
wouldn't have, 2010 presents you with options while you are typing, and I
just thought that I was unaware of those particular ReturnTypes in previous
versions, since I haven't made a great deal of use of WEEKDAY in the past.

Previous versions could use 2 in place of 11 in this instance.




"Mike H" wrote in message
...
Chip,

I played with that but here's my understanding

a1= 1 Jan 2010

and nothing in the holidays range

A1+100 returns 11/4/2010 which is a Sunday so we need to add one day. Both
your formula and mine return Monday 12/4/2010, exactly what the OP wants.

Now we add a holiday date of (say) 1 Feb 2010 in the holiday range. My
formula advances to 13/4/2010 but yours still returns 12/4/2010, in fact
it
doesn't seem to respond to any amount of dates in the holiday range. I'm
still sure there's a simpler way but unless i corrected the typo in your
formula incorrectly then this doesn't seem to be the answer.




--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Chip Pearson" wrote:


That could finish on a weekend date. There must be a simpler way but
until
then try this monstrosity

There is.

=WORKDAY(A1+100,--(WEEKDAY(A1+100,11)5),Holidays)

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Wed, 5 May 2010 13:28:03 -0700, Mike H
wrote:

Hmmm,

That could finish on a weekend date. There must be a simpler way but
until
then try this monstrosity

=A1+100+CHOOSE(WEEKDAY(A1+100,2),0,0,0,0,0,2,1)+S UMPRODUCT((Holidays=A1)*(Holidays<=A1+(100+SUMPRO DUCT((Holidays=A1)*(Holidays<=A1+100)))))+CHOOSE( WEEKDAY(A1+100+CHOOSE(WEEKDAY(A1+100,2),0,0,0,0,0, 2,1)+SUMPRODUCT((Holidays=A1)*(Holidays<=A1+(100+ SUMPRODUCT((Holidays=A1)*(Holidays<=A1+100))))),2 ),0,0,0,0,0,2,1)
.