# Formula to get month from week number

#### NikToo

##### Board Regular
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:

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?

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Tetra201

##### MrExcel MVP
See if the following formula works for you:
Excel Formula:
``=TEXT(WORKDAY.INTL(DATE(A2,1,0),B2,"1111110"),"mmm")``

#### NikToo

##### Board Regular
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

##### MrExcel MVP
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

##### Well-known Member
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?

Replies
1
Views
72
Replies
11
Views
198
Replies
5
Views
100
Replies
12
Views
497
Replies
6
Views
460

1,171,192
Messages
5,874,266
Members
433,042
Latest member
mcm2022

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back