All,
I am having the hardest time wrapping my head around this so i have come here for help. I am basically wanting to input any given date and a cell, and have the cell next to it display the week number within the month that date falls on. But there is a catch to it; the week numbers spread across two months depending on the given date.
Basic Rules:
1. Dates that fall at the beginning of the month that are on a Sunday, Monday, Tuesday, or Wednesday are considered a part of the first week of the date's current month.
2. Dates that fall at the beginning of the month that are on a Thursday, Friday, or Saturday, are considered a part of the last week of the previous month.
3. Dates that fall at the end of the month that are on a Sunday, Monday, or Tuesday are considered a part of the first week of the next month.
4. Dates that fall at the end of the month that are on a Wednesday, Thursday, Friday, or Saturday, are considered a part of the last week of the date's current month.
5. Dates that fall in the middle of the month are counted based on rules 1 and 2.
6. Dates that fall in the middle of the month, the next week begins on Mondays. For example: Jan 6 is still in week 1, Jan 7 starts week 2.
I have no idea how to come up with a formula for this or if it can even be done.
In my samples below, i am using the 2013 calendar year.
Dates at the beginning of the month examples:
Jan-1 = Week1 of January
Feb-1 = Week5 of January
May-1 = Week1 of May
Aug-1 = Week 5 of July
Dates at the end of the month examples:
Jan-31 = Week5 of January
April-30 = Week1 of May
July-31 = Week5 of July
Bookmarks