How many fridays in any given month?

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: How many fridays in any given month?

  1. #1
    New Member
    Join Date
    Jun 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    How can I get the number of Fridays in any given month starting from any given date?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,933
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello and welcome to the board

    This formula appears to work. It assumes that the start date is in cell A2. In order to use this the Analysis Toolpak must be installed. You can do this by clicking Tools, Add-ins and then checking the Analysis Toolpak addin.

    =IF(WEEKDAY(A2)=6,INT((EOMONTH(A2,0)-A2)/7)+1,INT((EOMONTH(A2,0)-A2)/7))[/i]

    HTH,
    Dan

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello, do you need the TOTAL number of fridays in the month ? or the ones that are left from the "given date" ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you need the total fridays in the month, no matter what, try this:

    =4+(WEEKDAY(DATE(YEAR(A2),MONTH(A2)+1,0),2)>=5)
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    New Member
    Join Date
    Jun 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank You guys for your help. But neither formular works for the whole year.

    Pablo's formula works for the whole year except for the month of June.

    DK's formula works on June but is off on other months.

    I need to find how many fridays in each month no mather what the given date is for that month.

  6. #6

    Join Date
    Feb 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There must surely be a simpler formula but this seems to work :-

    =1+INT((EOMONTH(A2,0)-(DATE(YEAR(A2),MONTH(A2),1)+MOD(6-WEEKDAY(DATE(YEAR(A2),MONTH(A2),1)),7)))/7)



    EDIT : This is a bit shorter :-

    =1+INT((EOMONTH(A2,0)-(EOMONTH(A2,-1)+1+MOD(6-WEEKDAY(EOMONTH(A2,-1)+1),7)))/7)


    [ This Message was edited by: Tikas A. Planck on 2002-06-08 08:13 ]

    [ This Message was edited by: Tikas A. Planck on 2002-06-08 15:12 ]

    [ This Message was edited by: Tikas A. Planck on 2002-06-08 15:13 ]

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,462
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-06-08 06:21, logique wrote:
    Thank You guys for your help. But neither formular works for the whole year.

    Pablo's formula works for the whole year except for the month of June.

    DK's formula works on June but is off on other months.

    I need to find how many fridays in each month no mather what the given date is for that month.
    =SUMPRODUCT((WEEKDAY(DATE(YEAR(A2),MONTH(A2),ROW(INDIRECT("1:"&DAY(DATE(YEAR(A2),MONTH(A2)+1,0))))))=6)+0)

    where A2 houses a date and 6 is the serial number that refers to Friday.


  8. #8
    New Member
    Join Date
    Jun 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks again guys.

    Problem solved.

    Planck's formula does what I need.

  9. #9
    New Member
    Join Date
    Jun 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin's formula also works.

    Thank You!

  10. #10

    Join Date
    Feb 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-06-08 08:31, logique wrote:
    Thanks again guys.

    Problem solved.

    Planck's formula does what I need.

    My constant works too!

User Tag List

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