Results 1 to 6 of 6

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. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Keaau, Hawaii
    Posts
    240

    Default 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. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    2,378

    Default 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. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default 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
    =

    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. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,416

    Default 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
    =

    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. #5
    Board Regular
    Join Date
    Dec 2002
    Posts
    346

    Default Re: Number of Friday's between two dates

    Quote 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. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Keaau, Hawaii
    Posts
    240

    Default 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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com