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))
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 ...
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??
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))
******** ******************** ************************************************************************>
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
=
A B C D E F G H I J K L 1 6/20/2003 53 * * * * * * * * * * 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.
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
=
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.
Hi,Originally Posted by KniteMare
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)
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
Like this thread? Share it with others