# Calculate next workday after adding calendar days to date in cell

D

#### Darrell

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.

M

#### Mike H

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

#### 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)
--
Mike

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

S

#### Steve Dunn

Perhaps, untested:

=workday(A1+99,1)

S

#### Steve Dunn

Missed holidays

=workday(A1+99,1,Holidays)

Steve Dunn said:
Perhaps, untested:

=workday(A1+99,1)

C

#### Chip Pearson

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

#### Darrell

Hi Steve:

I tried that doesn't calculate correctly, thanks.

D

#### Darrell

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,D218)

Darrell

M

#### Mike H

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.

M

#### Mike H

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.

M

#### Mike H

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]

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

#### Steve Dunn

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]

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

#### Mike H

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

#### Chip Pearson

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