How to get monthname from a weeknumber

santnok

Board Regular
Joined
Jan 10, 2014
Messages
94
Hi, I have a table where a have one column with numbers from 1 - 53. This starts in B10. In C10 I want the monthname based on the weeknumber.
Numbers that are in colum B is just a plain general number. So what I like to do is in the Monthname column I want to show the monthname only one time. So if weeknumbers from
1 to 4 is January I like to show January only in week 1, so in week 5 I like to then show February. If a weeknumber go from one moth to another month, I like to show both monthname in that week.
Ex: in week 13 this year we go from March to April, I then like to show Mar/Apr in the C column.

Can someone help me out on how to write the code in Column C to make this happen?

The table I have looks like this to day, under this tabel I have created I tabel that show how I want i to look. :)

Row
B
C
9WeeknumberMonthname
101JAN
112JAN
123JAN
134JAN
145JAN
156FEB
167FEB
178FEB
189FEB
1910MAR
2011MAR
2112MAR
2213MAR
2314APR
2415APR
2516APR
2617APR
2718APR
2819MAI
2920MAI
3021MAI
3122MAI
3223JUN
3324JUN
3425JUN
3526JUN
3627JUL
3728JUL
3829JUL
3930JUL
4031JUL
4132AUG
4233AUG
4334AUG
4435AUG
4536SEP
4637SEP
4738SEP
4839SEP
4940SEP
5041OKT
5142OKT
5243OKT
5344OKT
5445NOV
5546NOV
5647NOV
5748NOV
5849DES
5950DES
6051DES
6152DES
6253DES

Like it to look like this, I have taken the calendar year for 2021, so this tabel is the real one :)

RowBC
9WeeknumberMonthname
101JAN
112
123
134
145FEB
156
167
178
189Mar
1910
2011
2112
2213Mar/Apr
2314
2415
2516
2617Apr/Mai
2718
2819
2920
3021
3122Mai/Jun
3223
3324
3425
3526Jun/Jul
3627
3728
3829
3930Jul/Aug
4031
4132
4233
4334
4435Aug/Sep
4536
4637
4738
4839Sep/Oct
4940
5041
5142
5243
5344Nov
5445
5546
5647
5748Nov/Des
5849
5950
6051
6152Des/ Jan 2022
6253

Please get some help on how to write this code :)
Thanks for all help and answers :)
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows
what is the date at the start of week 1?
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows
This will get you the text you need, but it show's the month against every week. The calculation to return a blank as shown above is eluding me! The value in B1 is the start date 04.01.2021.

Excel Formula:
=TEXT($B$1+(B10-1)*7,"MMM") & IF(MONTH($B$1+(B10-1)*7)<>MONTH($B$1+(B11-1)*7-1), "/"&TEXT($B$1+(B11-1)*7-1,"MMM"),"")
 
Solution

santnok

Board Regular
Joined
Jan 10, 2014
Messages
94

ADVERTISEMENT

Hi pjmorris,

Thank you so much for the help, the code work verry fine. :)
The important was to get the right monthname for the weeknumber :)

For that start date, is that also somthing we can make a code on based on the year? I have a cell that I write the year. That cell is C7
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows
delighted that that worked, but not sure what you mean by based on a year in C7 (I'm sure its possible)
 

santnok

Board Regular
Joined
Jan 10, 2014
Messages
94

ADVERTISEMENT

In the sheet In cell B7 I have the text: Calendar Year: in cell C7 I write the year 2021 :)
 

santnok

Board Regular
Joined
Jan 10, 2014
Messages
94
Hi again, I think a get the right code
Excel Formula:
=DATE(C7;1;7)- WEEKDAY(DATE(C7;1;1)-1;3)
:)
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,988
Office Version
  1. 2016
Platform
  1. Windows
if it works its good, I came up with something slightly different:

Excel Formula:
=DATE(C7,1,WEEKDAY(DATE(C7,1,1),3))

I can't think of a reason to prefer either solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,952
Messages
5,621,794
Members
415,856
Latest member
jimb2k

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