Prev: Percentages breakouts for Ranking
Next: Referencing a cell in other worksheet that is using autofit ro
From: Mike H on 5 May 2010 16:28
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.


"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)+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.
>
>
> "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
> >
From: Chip Pearson on 5 May 2010 17:06

>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
<MikeH(a)discussions.microsoft.com> 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)+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)
From: Darrell on 5 May 2010 17:19
Hi Steve:

I tried that doesn't calculate correctly, thanks.

"Steve Dunn" wrote:

> Missed holidays
>
> =workday(A1+99,1,Holidays)
>
>
> "Steve Dunn" <stunn(a)sky.com> wrote in message
> news:ACE1D29B-4704-470E-A29B-49BAE35CFC3A(a)microsoft.com...
> > Perhaps, untested:
> >
> > =workday(A1+99,1)
> >
> >
> >
> >
> > "Darrell" <Darrell(a)discussions.microsoft.com> wrote in message
> > news:266BDAC6-F404-410C-9EBC-174C5A94A6FC(a)microsoft.com...
> >>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
> >>
> >
>
From: Darrell on 5 May 2010 17:22
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)+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.
>
>
> "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)+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.
> >
> >
> > "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
> > >
From: Mike H on 5 May 2010 17:28
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" <stunn(a)sky.com> wrote in message
> news:ACE1D29B-4704-470E-A29B-49BAE35CFC3A(a)microsoft.com...
> > Perhaps, untested:
> >
> > =workday(A1+99,1)
> >
> >
> >
> >
> > "Darrell" <Darrell(a)discussions.microsoft.com> wrote in message
> > news:266BDAC6-F404-410C-9EBC-174C5A94A6FC(a)microsoft.com...
> >>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
> >>
> >
>
 |  Next  |  Last
Pages: 1 2
Prev: Percentages breakouts for Ranking
Next: Referencing a cell in other worksheet that is using autofit ro