# Thread: Work Weeks in Month Thanks: 0 Likes: 0

1. ## Work Weeks in Month

Hello, Great Excel Minds,

I am searching for a simple solution to display the particular work week of any given month. I have searched through many posts, but I have not seen the solution I need. Thank you for any help you can give.

Given that each work week begins on Monday, and that the work week for a month extends to the end of the week (Sunday) (i.e. the final work week of October 2011 begins on 10/31, so week 5 for October includes the dates 10/31/11 through 11/6/11), the following formula usually gives me the work week for any given date for the month:

=WEEKNUM(A2+1-WEEKDAY(A2,2),2)-WEEKNUM(A2+1-WEEKDAY(A2,2)-DAY(A2+1-WEEKDAY(A2,2))+1,2)

Where it breaks down is if the first day of the month is a Monday (like August of 2011 or October of 2012), where it returns a 0 for the week number. What piece of the puzzle am I overlooking?

Thank you in advance for any help that can be given.

2. ## Re: Work Weeks in Month

I'm not very good at coming up with elegant date functions, however, the below might work ?

Code:
`=ROUNDUP((1+A4-(DATE(YEAR(A4-WEEKDAY(A4,3)),MONTH(A4-WEEKDAY(A4,3)),1+7)-WEEKDAY(DATE(YEAR(A4-WEEKDAY(A4,3)),MONTH(A4-WEEKDAY(A4,3)),7-1))))/7,0)`
The above is just calculating days between current date and first Monday of month to which the date in A4 applies - divides by 7 and rounds up result.

I've no doubt about 90% of it is superfluous to those who are good at this type of thing!

3. ## Re: Work Weeks in Month

I'm not into "elegance", I'm into solutions!

Your formula works perfectly through, at least, 2015 for what I need it to do, so it's elegant enough for me.

Thank you so much for your help, DonkeyOte!

4. ## Re: Work Weeks in Month

This formula should give you the same results

=INT((6+DAY(A4-WEEKDAY(A4,3)))/7)

5. ## Re: Work Weeks in Month

That is beautiful, barry! If I were to make one from my head, it would be twice as long as DonkeyOte's!

barry, your date formula acumen is EXCELlent!!

6. ## Re: Work Weeks in Month

Originally Posted by mgirvin
barry, your date formula acumen is EXCELlent!!
I'll echo that... I knew I should have said 95%...

7. ## Re: Work Weeks in Month

That's funny, DonkeyOte!!

However, your acumen lies elsewhere, DonkeyOte. Like for example the “two-way reverse lookup with dates and time” formula that you helped me with a while back... That formula you gave me was EXCELlent sprinkled with lots of acumen on top!!

## User Tag List

#### Posting Permissions

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