Sequence of week number by month/date

[jl2509]

Hello All,

I have Months listed across a row starting in C3 with Jan, then F3 with Feb etc all at the same 2 column intervals (other data in between)
In cell C1 is the year "2023"
Is there a way to have a sequence of week numbers generated from the month and year. i.e in Cell C3 it shows "Jan" in C1 it shows "2023" so from cell C4 it should list the week numbers for that month?

Thanks in Advance

 

[Peter_SSs]

Can you give us some sample data and expected results with XL2BB?

 

[jl2509]

Apologies, I cannot install addons
I just want to be able to use sequence or array formula for the weeks of a month

 

[Peter_SSs]

I cannot install addons

In that case can you give us some sample data and expected results (for say 3 moths only) with copy/paste?

 

[jl2509]

The image shows the week of a month by the year in cell C1
The reason that week 5 shows in both column C and F is because the end of Jan and start of Feb share the same week number

I know I could do this with a helper column of date and use the formula Weeknum but I dont really want additional data columns.
I am assuming that combining Sequence, Weeknum, with EOMONTH is the solution but I am lost with the actual formula.

The names in columns B and E are populated by another formula

 

[FormR]

Hi, here's one option you could try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1			2024
2
3			Jan			Feb			Mar			Apr
4			Week 1			Week 5			Week 9			Week 14
5			Week 2			Week 6			Week 10			Week 15
6			Week 3			Week 7			Week 11			Week 16
7			Week 4			Week 8			Week 12			Week 17
8			Week 5			Week 9			Week 13			Week 18
9									Week 14
10
11
12
Sheet1

Cell Formulas
Range	Formula
C4:C8,L4:L8,I4:I9,F4:F8	C4	=LET(D,DATE($C$1,MONTH(1&C3),1),"Week "&UNIQUE(WEEKNUM(D+SEQUENCE(DAY(EOMONTH(D,0)),,0))))
Dynamic array formulas.

 

[FormR]

Here's another option, slightly longer, but probably a little more performant.

=LET(D,DATE($C$1,MONTH(1&C3),1),FW,WEEKNUM(D),LW,WEEKNUM(EOMONTH(D,0)),"Week "&SEQUENCE(LW-FW+1,,FW,1))

 

[jl2509]

FormR,
Great solution. I went with option 2.

Just a side question, using sequence how do you list the number of weeks in a year, from the Start of C1 to End of C1.

 

[FormR]

Just a side question, using sequence how do you list the number of weeks in a year, from the Start of C1 to End of C1.

Something like this maybe:

=SEQUENCE(WEEKNUM(DATE(C1,12,31)))

 

[jl2509]

Nice Thank you