>>>>>Hi,
>>>>>
>>>>>I am trying to create a function that will return the week number in a month, by any date of Monday.
>>>>>
>>>>>For example, the date "08/21/2017" (in American system) it would be 3rd week (in August). The week has to have Monday. So this month (August) the first week is the week of August 7, August 14 is week 2, August 21 is week 3, and August 28 is week 4.
>>>>>
>>>>>My code is very convoluted/complicated and I am sure there is a better way.
>>>>
>>>>What's the week number for 3rd of the month if the first monday is on 5th?
>>>
>>>I am sorry that I don't quite understand the question. Could you please give me an example?
>>
>>That was the example. What is the week of 2017-08-01, if first week is the one containing 7th? If you start with sunday (as customary in the US), the first week is 6th to 12th. What is, as per your requirements, the week number for the days between 1st and 5th?
>
>August 1st is NOT a Monday. The specification calls that the Date passed to the function is always Monday. So the valid date is August 7, July 31, and so on. And the week is defined by Monday, not Sunday. Please see my code, reply to myself for my approach for this function. I am just trying to see if there is a simpler and maybe even more accurate code.
Ah, I didn't understand that "week number in a month, by any date of Monday" is supposed to mean "for any monday passed as a parameter", I thought that for any given date it should look for the previous monday, and then decide that way.
Well, then, since you're sure it's the monday, try with int((day(tdDate)+6)/7).
IOW, check for the number of days since the 1st of the month on the last day of your week - that should contain all seven days of that week, plus any preceding days. So this formula should work, from what little I tried).