Week of month sequence across years

Question

I am using a solution from Leila Gharani on creating a sequence of week numbers across a list of dates:

=WEEKNUM(DATE(YEAR([@dates]),1,DAY([@dates]-WEEKDAY([@dates],3))))

Case 2: Using the WEEKDAY() function to reset the week number to 1 every time the first Monday of the next month is reached: https://www.xelplus.com/reset-week-number-every-month-excel/

While the solution works well, the sequencing does not correctly work across years. If possible, what modifications could I make to the formula to achieve this?

Answer 1

For me, it's easier not to use Weeknum. Basically I would find the date of the previous Monday and then find the number of complete weeks in the month up to that date:

=LET(prevMonday,B2-WEEKDAY(B2,3),QUOTIENT(DAY(prevMonday)-1,7)+1)

You don't have to use let, but hopefully it makes it more readable.