D

#### Darrell

on a weekend or holiday the formula will return the next workday vs.

returning a weekend date.

I tried the workday function but it counted 100 workdays not calendar days.

Thanks in advance

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

D

on a weekend or holiday the formula will return the next workday vs.

returning a weekend date.

I tried the workday function but it counted 100 workdays not calendar days.

Thanks in advance

M

One way. Holidays is a named range containing your holiday dates

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

--

Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that

introduces the fewest assumptions while still sufficiently answering the

question.

M

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)+SUMPRODUCT((Holidays>=A1)*(Holidays<=A1+(100+SUMPRODUCT((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)

--

Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that

introduces the fewest assumptions while still sufficiently answering the

question.

S

Perhaps, untested:

=workday(A1+99,1)

=workday(A1+99,1)

S

Missed holidays

=workday(A1+99,1,Holidays)

=workday(A1+99,1,Holidays)

Steve Dunn said:Perhaps, untested:

=workday(A1+99,1)

C

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]

D

Hi Steve:

I tried that doesn't calculate correctly, thanks.

I tried that doesn't calculate correctly, thanks.

D

Great effort on my part. I tried the formula below using the fx insert

function but its seems to be counting twice. I wasn't able to substitute the

propert cells in your monster below.

=B3+100+NETWORKDAYS(B3,100,D218)

Thanks in advance.

Darrell

M

Much simpler than mine but it doesn't quite work, try this modification

=WORKDAY(A1+100,0,Holidays)+CHOOSE(WEEKDAY(WORKDAY(A1+100,0,Holidays),2),0,0,0,0,0,2,1)

--

Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that

introduces the fewest assumptions while still sufficiently answering the

question.

M

--

Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that

introduces the fewest assumptions while still sufficiently answering the

question.

M

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 said: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]

.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)+SUMPRODUCT((Holidays>=A1)*(Holidays<=A1+(100+SUMPRODUCT((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)

S

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 said:

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 said: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]

.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)+SUMPRODUCT((Holidays>=A1)*(Holidays<=A1+(100+SUMPRODUCT((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)

M

Thanks for that, I'm not familiar with E2010

--

Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that

introduces the fewest assumptions while still sufficiently answering the

question.

Steve Dunn said: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 said: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 said: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

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)+SUMPRODUCT((Holidays>=A1)*(Holidays<=A1+(100+SUMPRODUCT((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)

.

C

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]

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 said: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 said: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

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)+SUMPRODUCT((Holidays>=A1)*(Holidays<=A1+(100+SUMPRODUCT((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)

.

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.