I want to add 100 calendar days to a date in a cell and when that date falls
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

Darrell,

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)+SU MPRODUCT((Holidays=A1)*(Holidays<=A1+(100+SUMPROD UCT((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.


"Darrell" wrote:

I want to add 100 calendar days to a date in a cell and when that date falls
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

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)+SU MPRODUCT((Holidays=A1)*(Holidays<=A1+(100+SUMPROD UCT((Holidays=A1)*(Holidays<=A1+100)))))+CHOOSE(W EEKDAY(A1+100+CHOOSE(WEEKDAY(A1+100,2),0,0,0,0,0,2 ,1)+SUMPRODUCT((Holidays=A1)*(Holidays<=A1+(100+S UMPRODUCT((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.


"Mike H" wrote:

Darrell,

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)+SU MPRODUCT((Holidays=A1)*(Holidays<=A1+(100+SUMPROD UCT((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.


"Darrell" wrote:

I want to add 100 calendar days to a date in a cell and when that date falls
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

Perhaps, untested:

=workday(A1+99,1)




"Darrell" wrote in message
...
I want to add 100 calendar days to a date in a cell and when that date
falls
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

Missed holidays

=workday(A1+99,1,Holidays)


"Steve Dunn" wrote in message
...
Perhaps, untested:

=workday(A1+99,1)




"Darrell" wrote in message
...
I want to add 100 calendar days to a date in a cell and when that date
falls
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

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)

Hi Steve:

I tried that doesn't calculate correctly, thanks.

"Steve Dunn" wrote:

Missed holidays

=workday(A1+99,1,Holidays)


"Steve Dunn" wrote in message
...
Perhaps, untested:

=workday(A1+99,1)




"Darrell" wrote in message
...
I want to add 100 calendar days to a date in a cell and when that date
falls
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

Hi Mike

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,D2:D18)

Thanks in advance.
Darrell

"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)+SU MPRODUCT((Holidays=A1)*(Holidays<=A1+(100+SUMPROD UCT((Holidays=A1)*(Holidays<=A1+100)))))+CHOOSE(W EEKDAY(A1+100+CHOOSE(WEEKDAY(A1+100,2),0,0,0,0,0,2 ,1)+SUMPRODUCT((Holidays=A1)*(Holidays<=A1+(100+S UMPRODUCT((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.


"Mike H" wrote:

Darrell,

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)+SU MPRODUCT((Holidays=A1)*(Holidays<=A1+(100+SUMPROD UCT((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.


"Darrell" wrote:

I want to add 100 calendar days to a date in a cell and when that date falls
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

Steve,

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.


"Steve Dunn" wrote:

Missed holidays

=workday(A1+99,1,Holidays)


"Steve Dunn" wrote in message
...
Perhaps, untested:

=workday(A1+99,1)




"Darrell" wrote in message
...
I want to add 100 calendar days to a date in a cell and when that date
falls
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

see my response to Steve, he nearly cracked it
--
Mike

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


"Darrell" wrote:

Hi Mike

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,D2:D18)

Thanks in advance.
Darrell

"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)+SU MPRODUCT((Holidays=A1)*(Holidays<=A1+(100+SUMPROD UCT((Holidays=A1)*(Holidays<=A1+100)))))+CHOOSE(W EEKDAY(A1+100+CHOOSE(WEEKDAY(A1+100,2),0,0,0,0,0,2 ,1)+SUMPRODUCT((Holidays=A1)*(Holidays<=A1+(100+S UMPRODUCT((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.


"Mike H" wrote:

Darrell,

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)+SU MPRODUCT((Holidays=A1)*(Holidays<=A1+(100+SUMPROD UCT((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.


"Darrell" wrote:

I want to add 100 calendar days to a date in a cell and when that date falls
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

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

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

Steve,

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

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