How to get monthname from a weeknumber

[santnok]

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​
9	Weeknumber	Monthname
10	1	JAN
11	2	JAN
12	3	JAN
13	4	JAN
14	5	JAN
15	6	FEB
16	7	FEB
17	8	FEB
18	9	FEB
19	10	MAR
20	11	MAR
21	12	MAR
22	13	MAR
23	14	APR
24	15	APR
25	16	APR
26	17	APR
27	18	APR
28	19	MAI
29	20	MAI
30	21	MAI
31	22	MAI
32	23	JUN
33	24	JUN
34	25	JUN
35	26	JUN
36	27	JUL
37	28	JUL
38	29	JUL
39	30	JUL
40	31	JUL
41	32	AUG
42	33	AUG
43	34	AUG
44	35	AUG
45	36	SEP
46	37	SEP
47	38	SEP
48	39	SEP
49	40	SEP
50	41	OKT
51	42	OKT
52	43	OKT
53	44	OKT
54	45	NOV
55	46	NOV
56	47	NOV
57	48	NOV
58	49	DES
59	50	DES
60	51	DES
61	52	DES
62	53	DES

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

Row	B	C
9	Weeknumber	Monthname
10	1	JAN
11	2
12	3
13	4
14	5	FEB
15	6
16	7
17	8
18	9	Mar
19	10
20	11
21	12
22	13	Mar/Apr
23	14
24	15
25	16
26	17	Apr/Mai
27	18
28	19
29	20
30	21
31	22	Mai/Jun
32	23
33	24
34	25
35	26	Jun/Jul
36	27
37	28
38	29
39	30	Jul/Aug
40	31
41	32
42	33
43	34
44	35	Aug/Sep
45	36
46	37
47	38
48	39	Sep/Oct
49	40
50	41
51	42
52	43
53	44	Nov
54	45
55	46
56	47
57	48	Nov/Des
58	49
59	50
60	51
61	52	Des/ Jan 2022
62	53

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

 

[pjmorris]

what is the date at the start of week 1?

 

[santnok]

For 2021 it was 04.01.2020 it was on monday

 

[pjmorris]

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.

=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"),"")

 

[santnok]

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]

delighted that that worked, but not sure what you mean by based on a year in C7 (I'm sure its possible)

 

[santnok]

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

 

[santnok]

Hi again, I think a get the right code

=DATE(C7;1;7)- WEEKDAY(DATE(C7;1;1)-1;3)

 

[pjmorris]

if it works its good, I came up with something slightly different:

=DATE(C7,1,WEEKDAY(DATE(C7,1,1),3))

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

 

[santnok]

Thank you again pjmorris Your code was smaller and better
I found my code from this site List First Monday Date in Each Month • My Online Training Hub that I modifed litlebit.

Have a great day