# Thread: Count weeks in Month (2K3)

1. ## 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?

2. ## 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. ## 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

4. ## 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>

5. ## 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>

6. ## 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. ## 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.

8. ## 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
•