Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Count weeks in Month (2K3)

    Can't seem to find a formula that calculates the number of weeks in a given month. I would like it to come out as a number ie 4 or 5 so I can use it in an if statement?
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count weeks in Month (2K3)

    Which definition of 'week' do you want to use? Arbitrary 7-day periods or calendar weeks? If the latter - do you prefer the US definition (week begins on Sunday) or the ISO one (week begins on Monday) or something else?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count weeks in Month (2K3)

    Ooh that is a good question. I would like to do it on calendar weeks.

    I have done this to find the third Thursday

    =DATE(YEAR(A2),MONTH(A2),14+CHOOSE(WEEKDAY(DATE(YE AR(A2),MONTH(A2),5)),2,1,7,6,5,4,3))

    But obviously it will not find the last Thursday if it is a 5 week month. This equation uses the US definition as Thursday is day 5...lets stick to this.

    To give you an idea of what is happening, the business requires me to do a server config change every month. I have a cut off day of these requests to be the penultimate Thursday of each month....this may be an indicator to what I am looking for in the equation hopefully
    Jerry

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count weeks in Month (2K3)

    If A1 contains a date which is the first of a month, then this formula should retrun the date of the last Thursday:

    <code>
    =IF(MONTH(A1-WEEKDAY(A1,1)+33)=MONTH(A1),A1-WEEKDAY(A1,1)+33,A1-WEEKDAY(A1,1)+26)
    </code>
    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count weeks in Month (2K3)

    Fantastic, I can go home now <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  6. #6
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count weeks in Month (2K3)

    Legare, why the use of 33 and 26 in the formula? I understand the Month and Weekday function, but don't understand the use of 33 and 26. How did you determine the usage of these numbers?

    Thanks.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count weeks in Month (2K3)

    I first subtract the WEEKDAY (with Sunday as the first day of the week) from the date of the first of the month. I then add 5 to that to the next Thursday and then add 28 (four weeks) to that to get to the fourth thursday after that. 5+28=33. Three weeks is 21 days and 5+21=26.
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count weeks in Month (2K3)

    Thanks for the explantion. I can now see how one could modify the formula for other days.

Posting Permissions

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