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