Work Weeks in Month
Results 1 to 7 of 7

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

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!
    Does my a$$ look big in this picture ?

  3. #3
    New Member
    Join Date
    Sep 2008
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Work Weeks in Month

    This formula should give you the same results

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

  5. #5
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,212
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default 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!!
    Sincerely, Mike Girvin

  6. #6
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Work Weeks in Month

    Quote Originally Posted by mgirvin
    barry, your date formula acumen is EXCELlent!!
    I'll echo that... I knew I should have said 95%...
    Does my a$$ look big in this picture ?

  7. #7
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,212
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default 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!!

    Sincerely, Mike Girvin

Some videos you may like

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
  •