Prev: slection change to make the selection go down 1 cell
Next: loop VBA request
From: Rick Rothstein on 25 Apr 2010 16:10
A couple of comments about the text description for your formula solution
and for the functions I posted to you.

In the description for your formula, you allude to it being equivalent to
NETWORKDAYS, but you forgot to mention that it does not factor in holidays.
You also mentioned NETWORKDAYS is from the "analysis add-in"... actually, to
be technically correct, it is the "Analysis ToolPak" add-in. I don't know
whether you might want to mention that NETWORKDAYS is built into Excel
2007/2010 and is part of the "Analysis ToolPak" add-in for Excel 2003 and
earlier.

With regard to my functions, the first one (without the NWD parameter) also
has the requirement that D1 needs to be the earlier date and D2 the later
date. So you may want to use this version of that function instead...

Function CWD(StartDate As Date, EndDate As Date) As Long
CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate,
EndDate) * 2 - _
(Weekday(EndDate) <> 7) + (Weekday(StartDate) = 1) +
(Weekday(StartDate, 2) < 6)
End Function

As for the presentation of both of my functions, you show them one following
the other; however, since they both have the same name, there might be a
chance that less experienced VB coders will be confused. Perhaps you could
change the lead in description to my function to this...

"Rick Rothstein came up with an elegant equivalent VBA version:"

where I left the word "elegant" in because you used it, not because I would
necessarily have described it that way. Then, you could use something like
the following text to lead into the second function...

"Rick also provide this more general version which, if the optional NWD
parameter is omitted or set to False, will return the same values as the
above version of CWD and if the optional NWD parameter is set to True, then
it will return the same results as Excel's NETWORKDAYS function (again,
without consideration for holidays):"

--
Rick (MVP - Excel)



"Bernd P" <bplumhoff(a)gmail.com> wrote in message
news:bcef26ab-edb3-4674-986b-3df3aef71ab6(a)v14g2000yqb.googlegroups.com...
> Hello Rick,
>
> Great. Tested and published as well. No need anymore for my cwd
> version. Thanks.
>
> Regards,
> Bernd
>
> On 25 Apr., 21:15, "Rick Rothstein"
> <rick.newsNO.S...(a)NO.SPAMverizon.net> wrote:
>> Sorry, it looks like I implemented the NWD parameter backwards. This
>> should
>> produce the correct results for the NWD parameter equaling True and
>> False...
>>
>> Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long
>> CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - (Weekday(D2)
>> _
>> <> 7) + (Weekday(D1) = 1) - (Not NWD) * (Weekday(D1, 2) < 6)
>> End Function
>>
>> And here it is with the renamed parameters to enforce the requirement
>> that
>> D1 must be the earlier date and D2 the later one...
>>
>> Function CWD(StartDate As Date, EndDate As Date, _
>> Optional NWD As Boolean) As Long
>> CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, _
>> EndDate) * 2 - (Weekday(EndDate) <> 7) + (Weekday(StartDate) = 1)
>> _
>> - (Not NWD) * (Weekday(StartDate, 2) < 6)
>> End Function
>>
>> --
>> Rick (MVP - Excel)

From: Bernd P on 26 Apr 2010 01:39
Hello Rick,

Agreed and changed, thanks.

Let us find another "challenge" :-)

Regards,
Bernd

On 25 Apr., 22:10, "Rick Rothstein"
<rick.newsNO.S...(a)NO.SPAMverizon.net> wrote:
> A couple of comments about the text description for your formula solution
> and for the functions I posted to you.
>
> In the description for your formula, you allude to it being equivalent to
> NETWORKDAYS, but you forgot to mention that it does not factor in holidays.
> You also mentioned NETWORKDAYS is from the "analysis add-in"... actually, to
> be technically correct, it is the "Analysis ToolPak" add-in. I don't know
> whether you might want to mention that NETWORKDAYS is built into Excel
> 2007/2010 and is part of the "Analysis ToolPak" add-in for Excel 2003 and
> earlier.
>
> With regard to my functions, the first one (without the NWD parameter) also
> has the requirement that D1 needs to be the earlier date and D2 the later
> date. So you may want to use this version of that function instead...
>
> Function CWD(StartDate As Date, EndDate As Date) As Long
>   CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate,
> EndDate) * 2 - _
>         (Weekday(EndDate) <> 7) + (Weekday(StartDate) = 1) +
> (Weekday(StartDate, 2) < 6)
> End Function
>
> As for the presentation of both of my functions, you show them one following
> the other; however, since they both have the same name, there might be a
> chance that less experienced VB coders will be confused. Perhaps you could
> change the lead in description to my function to this...
>
> "Rick Rothstein came up with an elegant equivalent VBA version:"
>
> where I left the word "elegant" in because you used it, not because I would
> necessarily have described it that way. Then, you could use something like
> the following text to lead into the second function...
>
> "Rick also provide this more general version which, if the optional NWD
> parameter is omitted or set to False, will return the same values as the
> above version of CWD and if the optional NWD parameter is set to True, then
> it will return the same results as Excel's NETWORKDAYS function (again,
> without consideration for holidays):"
>
> --
> Rick (MVP - Excel)
>
> "Bernd P" <bplumh...(a)gmail.com> wrote in message
>
> news:bcef26ab-edb3-4674-986b-3df3aef71ab6(a)v14g2000yqb.googlegroups.com...
>
> > Hello Rick,
>
> > Great. Tested and published as well. No need anymore for my cwd
> > version. Thanks.
>
> > Regards,
> > Bernd
>
> > On 25 Apr., 21:15, "Rick Rothstein"
> > <rick.newsNO.S...(a)NO.SPAMverizon.net> wrote:
> >> Sorry, it looks like I implemented the NWD parameter backwards. This
> >> should
> >> produce the correct results for the NWD parameter equaling True and
> >> False...
>
> >> Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long
> >>   CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - (Weekday(D2)
> >> _
> >>              <> 7) + (Weekday(D1) = 1) - (Not NWD) * (Weekday(D1, 2) < 6)
> >> End Function
>
> >> And here it is with the renamed parameters to enforce the requirement
> >> that
> >> D1 must be the earlier date and D2 the later one...
>
> >> Function CWD(StartDate As Date, EndDate As Date, _
> >>              Optional NWD As Boolean) As Long
> >>   CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, _
> >>         EndDate) * 2 - (Weekday(EndDate) <> 7) + (Weekday(StartDate) = 1)
> >> _
> >>         - (Not NWD) * (Weekday(StartDate, 2) < 6)
> >> End Function
>
> >> --
> >> Rick (MVP - Excel)

First  |  Prev  | 
Pages: 1 2 3
Prev: slection change to make the selection go down 1 cell
Next: loop VBA request