Find Week Number For Month

[t0ny84]

Hi,

I am currently using the formula to obtain the specified first Monday of each month.
DATE(Yr,MM,1+7*Nth)-WEEKDAY(DATE(Yr,MM,8-DoW))

I am wondering if anyone knows how to calculate the Week Number just for that Month.

My aim is to have something like
If WeekNumber = 1st Week AND WeekDay = 2 Then the formula DATE(Yr,MM,1+7*Nth)-WEEKDAY(DATE(Yr,MM,8-DoW))
would adjust to show the 1st Monday of the month and so on.
Thanks in advance,

t0ny84

 

[t0ny84]

Update:
I found this site which does exactly what I need - Get Week Number in Excel & Reset for Every Month

So now my query is, how can I reduce the lines for the below code as I would be using this for 7 days so would need the same for each day of the week.

Using this formula in a cell for the WeekNumber
Finds the Week Number of Month
=IF(WEEKDAY(K3,2)>5,"",WEEKNUM(K3,16)-WEEKNUM(EOMONTH(K3,-1)+1,16)+1)

Using this formula in a cell for the Weekday (Based on Monday being 1 and Sunday being 7)
=WEEKDAY(K3,2)

VBA would ideally check both of cells holding the two formulas above and then copy based on the results.

If WeekDay = 1 and WeekNumber = 1 then 
Sheet2.Range("Week1 Monday Cell Reference")
ElseIf
WeekDay = 1 and WeekNumber = 2 then
Sheet2.Range("Week2 Monday Cell Reference")
ElseIf
WeekDay = 1 and WeekNumber = 3 then
Sheet2.Range("Week3 Monday Cell Reference")
Elseif
Weekday = 1 and WeekNumber = 4 then
Sheet2.Range("Week4 Monday Cell Reference")
ElseIf 
Weekday = 1 and WeekNumber = 5 then
Sheet2.Range("Week5 Monday Cell Reference")

t0ny84