Prev: Percentages breakouts for Ranking
Next: Referencing a cell in other worksheet that is using autofit ro
From: Mike H on 5 May 2010 18:05
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
> <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: Steve Dunn on 6 May 2010 03:30
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" <MikeH(a)discussions.microsoft.com> wrote in message
news:09F5E2BB-2F7B-40D3-839C-02978FF75EB5(a)microsoft.com...
> 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
>> <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: Mike H on 6 May 2010 04:34
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" <MikeH(a)discussions.microsoft.com> wrote in message
> news:09F5E2BB-2F7B-40D3-839C-02978FF75EB5(a)microsoft.com...
> > 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
> >> <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: Chip Pearson on 6 May 2010 19:04

>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" <stunn(a)sky.com> 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" <MikeH(a)discussions.microsoft.com> wrote in message
>news:09F5E2BB-2F7B-40D3-839C-02978FF75EB5(a)microsoft.com...
>> 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
>>> <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)
>>> .
>>>
First  |  Prev  | 
Pages: 1 2
Prev: Percentages breakouts for Ranking
Next: Referencing a cell in other worksheet that is using autofit ro