Display Week Number based on special requirements

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

I had a pretty difficult time with this, but here is my solution. I used a helper cell to calculate this, because it will keep your formula a lot cleaner.
Put your Date in cell B2.
You can determine the Wednesday of the current week with the following formula in D2:

Formula:

Please Login or Register  to view this content.

Using this Wednesday date, you can calculate the weeknumber of the current month with the following formula in F2:

Formula:

Please Login or Register  to view this content.

That is much closer than what i came up with!

But try 4/30 in your formula, it shows it as week5 of april but looking at the rules (#3), it should be week1 of may.

One more rule i forgot to add here ( i am editing my main post to update the rules). The next week begins on Mondays for dates that fall in the middle of the month. For example: Jan 6 is still in week 1, Jan 7 starts week 2.

It appears as week #1 for me.
I changed the formula to account for weeks beginning on Monday.
Let me know if what is in the attached file is correct.

That is because the 1st is on a sunday, and is obviously week #1, but on Monday a new week starts, in this case week #2.
How do you fix that? I am not sure.

I believe this only happens if the year starts on a sunday. Assuming that, this formula seems to work:

Formula:

Please Login or Register  to view this content.

The additional formula simply subtracts 1 from the week # IF:
1. the week month is January
2. The first day of the year starts on a Sunday

You should be able to simplify this somewhat. If you have a date in B2 get the week number with this formula

=INT((6+DAY(B2+3-WEEKDAY(B2-1)))/7)

using that formula week 1 always starts with the Monday that falls somewhere in the 7 days which includes the last 2 days of the month and the first 5 of the next - that seems to match your requirements although I think your initial description is a bit confusing.....