Need a formula to count the fridays between two dates

[tiq]

example:
cell F1 is the start date 07-01-06
cell E1 is the end date 07-29-06
cell F3 is the answer 4

I just need to count the fridays between two dates

[VBA Noob]

Try

=SUM(IF(WEEKDAY(F2-1+ROW(INDIRECT("1:"&TRUNC(F3-F2)+1)))=6,1,0))

It's an array so need to press Crtl + Shitf + enter to work

VBA Noob

[Bob Phillips]

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(F1&":"&E1)))=6))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"tiq" <[email protected]> wrote in message
news:[email protected]...
> example:
> cell F1 is the start date 07-01-06
> cell E1 is the end date 07-29-06
> cell F3 is the answer 4
>
> I just need to count the fridays between two dates

[Jim Thomlinson]

Here is an exerpt from Chip Pearson's Site...
*****************************************
Number Of Mondays In Period

If you need to return the number of Monday's (or any other day) that occur
within an interval between two dates, use the following Array Formula:

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)
*****************************************************
Being an Array formula it must be commited with Ctrl+Shift+Enter when it is
typed in...

Here is a link to Chip's site.

http://www.cpearson.com/excel/DateTimeWS.htm
--
HTH...

Jim Thomlinson


"tiq" wrote:

> example:
> cell F1 is the start date 07-01-06
> cell E1 is the end date 07-29-06
> cell F3 is the answer 4
>
> I just need to count the fridays between two dates

[Bob Phillips]

Mine's a bit more obvious Jim <vbg>

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> Here is an exerpt from Chip Pearson's Site...
> *****************************************
> Number Of Mondays In Period
>
> If you need to return the number of Monday's (or any other day) that occur
> within an interval between two dates, use the following Array Formula:
>
> =SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))
>
> This formula assumes the following:
> A2 contains the beginning date of the interval
> B2 contains the ending date of the interval
> C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)
> *****************************************************
> Being an Array formula it must be commited with Ctrl+Shift+Enter when it
is
> typed in...
>
> Here is a link to Chip's site.
>
> http://www.cpearson.com/excel/DateTimeWS.htm
> --
> HTH...
>
> Jim Thomlinson
>
>
> "tiq" wrote:
>
> > example:
> > cell F1 is the start date 07-01-06
> > cell E1 is the end date 07-29-06
> > cell F3 is the answer 4
> >
> > I just need to count the fridays between two dates

[Bearacade]

Just because I like to make things difficult =)

=IF(6-WEEKDAY(F1)>-1, QUOTIENT((E1-F1-(6-WEEKDAY(F1))),7)+1, QUOTIENT((E1-F1-6), 7)+1)

[Jim Thomlinson]

Almost intuitive. We should let Chip know...<vbg>
--
HTH...

Jim Thomlinson


"Bob Phillips" wrote:

> Mine's a bit more obvious Jim <vbg>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
> > Here is an exerpt from Chip Pearson's Site...
> > *****************************************
> > Number Of Mondays In Period
> >
> > If you need to return the number of Monday's (or any other day) that occur
> > within an interval between two dates, use the following Array Formula:
> >
> > =SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))
> >
> > This formula assumes the following:
> > A2 contains the beginning date of the interval
> > B2 contains the ending date of the interval
> > C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)
> > *****************************************************
> > Being an Array formula it must be commited with Ctrl+Shift+Enter when it
> is
> > typed in...
> >
> > Here is a link to Chip's site.
> >
> > http://www.cpearson.com/excel/DateTimeWS.htm
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "tiq" wrote:
> >
> > > example:
> > > cell F1 is the start date 07-01-06
> > > cell E1 is the end date 07-29-06
> > > cell F3 is the answer 4
> > >
> > > I just need to count the fridays between two dates
>
>
>