Results 1 to 10 of 10

How many fridays in any given month?

This is a discussion on How many fridays in any given month? within the Excel Questions forums, part of the Question Forums category; How can I get the number of Fridays in any given month starting from any given date?...

  1. #1
    New Member
    Join Date
    Jun 2002
    Posts
    4

    Default

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

  2. #2
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

    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,950

    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,950

    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

    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

    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
    64,855

    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

    Default

    Thanks again guys.

    Problem solved.

    Planck's formula does what I need.

  9. #9
    New Member
    Join Date
    Jun 2002
    Posts
    4

    Default

    Aladin's formula also works.

    Thank You!

  10. #10

    Join Date
    Feb 2002
    Posts
    47

    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!

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