Formula to get month from week number

[NikToo]

This is causing me too much grief as hen I try one thing it fixes it in one place and breaks it in another.

I need the month number based on the week number, running from Sunday to Saturday, starting on the first Sunday of the year. So in 2020 it was January 5th which should start as week 1, in 2021 it's January 3rd.

At the moment this works for 2021 but not 2020:

=TEXT((MONTH(DATE(A2,1,B2*7-4)))*29,"mmm")

Year is in A2, week number in B2. I can't figure out what the formula does so I'm just trying different numbers, which isn't ideal.

Is there one formula which could sort out both years in one go?

 

[Tetra201]

See if the following formula works for you:

=TEXT(WORKDAY.INTL(DATE(A2,1,0),B2,"1111110"),"mmm")

 

[NikToo]

It does, thanks. Apart from week 53 in 2020 which comes up as January... I'll have to fudge that one manually.

What does the formula do?

 

[Tetra201]

The formula looks for the n-th Sunday of the year and returns its month.

For year 2020, the 53rd Sunday is January 3, 2021, so the formula returns "Jan".

 

[jasonb75]

It does, thanks. Apart from week 53 in 2020 which comes up as January...

What would you consider to be the correct result when the specified week is split over 2 months?