Number of Friday's between two dates

[KniteMare]

It is getting time to reset the Weekly Reports for the next Fiscal Year. I need to determine the number of friday's between the third friday of June Lasty Year and the next year. Sometimes this is 52 and sometimes it is 53. I want the code to return that number as a variable so I can have my Data base up date a colume that runs Week one through Week 52 or 53.

KniteMare
This is what I did so far it seems to be correct but I am not certain.

Sub weeker()
Dim a, b, c
a = Range("DateStart").Value
b = Range("DateEnd").Value
c = Range("Weeks").Value
c = a - b
c = Format(c, "ww")

End Sub

??FeedBack??

 

[Dave Patton]

Does your Sub check the number of Fridays?

VBA Datediff can calculate the number of weeks.

A formula that will calculate the number of Fridays in a date range follows

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(Start&":"&End)),2)=5))

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	6/20/2003	53
2
3
4
5
Sheet1

 

[Aladin Akyurek]

What follows includes formulas to compute the Start and End bits in the formula Dave (Patton) suggested...

Book4
	A	B	C	D
1	1999
2	Fri6/18/1999
3	Sun6/18/2000
4	53
5
6
Sheet1

A1: 1999, a year of interest.

A2:

=DATE(A1,6,1+7*3)-WEEKDAY(DATE(A1,6,8-6))

which you might want to rewrite as...

=DATE(A1,6,22)-WEEKDAY(DATE(A1,6,2))

A3:

=EDATE(A2,12)

which requires the Analysis Toolpak add-in.

A4:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&A3)))=6))

where WEEKDAY's optional argument is omitted so that Friday is numbered as 6.

If so desired, you can substitute the formulas in A2 and A3 in the formula in A4.

 

[2rrs]

It is getting time to reset the Weekly Reports for the next Fiscal Year. I need to determine the number of friday's between the third friday of June Lasty Year and the next year. Sometimes this is 52 and sometimes it is 53. I want the code to return that number as a variable so I can have my Data base up date a colume that runs Week one through Week 52 or 53.

??FeedBack??

Hi,
This may help also;

From; http://www.cpearson.com/excel/DateTimeWS.htm


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)

 

[KniteMare]

WOW, Thanks all.

Ive been in meetings all day and just got back. I took a quick look at noon and did not understand the EDATE Thanks Aladin for the detailed explaination.

Every year, since this only need be done once per year. I COUNT the number of fridays and input that into a loop which creates the initial rows of wk1 through wk52 (or 53) I am trying to automate this task and was comming up with answers in my codeing of 52,53 or 1.

KniteMare