Need a formula to count the fridays between two dates

[Guest]

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)

 

[Guest]

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

 

[Bob Phillips]

Mine's a bit more obvious Jim <vbg>

--
HTH

Bob Phillips

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

 

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

 

[Guest]

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

 

[Guest]

Thanks. I used that and it worked for me. I was pretty amazed, I'm still
trying to figure it out.