Okay, I will try to explain, meanwhile changing my algorithm to US system.
See the attached workbook for reference.
Let's assume that the current month begins with Sunday, Week #1. This assumption holds for July, so it's a perfect month for us right now.
If I want to calculate which week a day is in, I need to use something like this formula:
Week = INT(DayOfMonth /7)
This is shown in column E. You can see that the 7th day belongs to the 2nd week, which is wrong, and is corrected in column F with the following formula:
Week = INT((DayOfMonth-1) /7)
Compare the yellow cells.
Now, if I want the 1st week to get an index of 1 instead zero, I have to add 1 to the result. See column G, and the following formula
Week = INT((DayOfMonth-1) /7)+1
This is, so far, all happy and works well, but once we reach August, it will give wrong results. See red cells in column G.
5th Aug, which is Sunday, should belong to Week 2, instead of Week 1.
This happens because August doesn't start with Sunday.
August starts with Wednesday, which is 4th day of the week, so it is, sort of, a 3 days offset to 1st day of the week (Sunday). This means, the whole month is shifted by 3 days. This is corrected in column H:
Week = INT((DayOfMonth-1+3) /7)+1
But column H is only valid for August, as September starts with Saturday, which is a 6 days offset. So, in order to get a formula that is valid for all and each months, you need a reference day in each month. The most simple choice is 1st day of the month.
So the formula is:
Week = INT((DayOfMonth-1 + Weekday(1st day of month) -1) /7)+1
'which is the same as
Week = INT((DayOfMonth + Weekday(1st day of month) -2) /7)+1
And it was here that I noticed Bob's post, and decided that the rest should be self-evident. (With some thinking, probably.)
Jimmy