Formula to get month from week number

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
54
Office Version
  1. 365
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,646
See if the following formula works for you:
Excel Formula:
=TEXT(WORKDAY.INTL(DATE(A2,1,0),B2,"1111110"),"mmm")
 

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
54
Office Version
  1. 365
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
Joined
Oct 14, 2016
Messages
3,646
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
Joined
Dec 30, 2008
Messages
12,910
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,565
Members
418,140
Latest member
ahepple86

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top