Number of Friday's between two dates

This is a discussion on Number of Friday's between two dates within the Excel Questions forums, part of the Question Forums category; It is getting time to reset the Weekly Reports for the next Fiscal Year. I need to determine the number ...

1. Number of Friday's between two dates

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??

2. Re: Number of Friday's between two dates

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

3. Re: Number of Friday's between two dates

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 B1 =

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

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

4. Re: Number of Friday's between two dates

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

******** ******************** ************************************************************************>
 Microsoft Excel - Book4 ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A2A3A4 =

A
B
C
D
1
1999***
2
Fri*6/18/1999***
3
Sun*6/18/2000***
4
53***
5
****
6
****
 Sheet1 *

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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.

5. Re: Number of Friday's between two dates

Originally Posted by 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.

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

6. Re: Number of Friday's between two dates

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

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•