Calculate next workday after adding calendar days to date in cell

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

Thanks in advance

 

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

 

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

 

[Steve Dunn]

Perhaps, untested:

=workday(A1+99,1)

 

[Steve Dunn]

Missed holidays

=workday(A1+99,1,Holidays)

Perhaps, untested:

=workday(A1+99,1)

 

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

 

[Darrell]

Hi Steve:

I tried that doesn't calculate correctly, thanks.

 

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

Thanks in advance.
Darrell

 

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

 

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

 

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

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)

.

 

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

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.

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)

.

 

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

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.

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.

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

 

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

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.

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