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:
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?
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:
Excel Formula:
=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?