You're on the right track with the WEEKNUM function, but the formula is rather complex. To achieve what you want, you'll need to use the following formula:
This formula extracts the week of the year (44 for the 1st of November) and then subtracts it from the week of the first day in the month to get the week of the month.
You can change the day that weeks begin by changing the second argument of both WEEKNUM functions (set to 2 in the above example).
For weeks beginning on Sunday, use: =WEEKNUM(A1,1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),1)+1
For weeks beginning on Tuesday, use: =WEEKNUM(A1,12)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),1),12)+1
For more about how dates are processed in Excel, see Essential Skills Lesson 4-2: Understand date serial numbers.
I have attached an example spreadsheet showing the formula in action:
If you need any more assistance, feel free to reply.