Assigning recurring weeks that reset every month

[BBxcl]

I have a set of dates say in column A and want to associate custom week numbers that reset after every 5 weeks.

So for example, the week counting starts from 27/09/21 (Monday) and will end on 31/10/21 (Sunday) making it 5 weeks. For dates after 31st of October, I’d want to reset the week number to 1 and count up to 5 again. Similarly, for dates before 27th of September, the week numbers should decrease from 5 to 1 and reset again.

I’ve been trying to play around with WeekNum() for hours but haven’t managed to figure out a way to do this. Is there a way to dynamically assign custom recurring week numbers like that?

Any help would be greatly appreciated, thanks

 

[steve the fish]

This will create a sequence of 20 numbers starting from 1 with the sequence 1,2,3,4,5,1,2,3,4,5 etc.

=MOD(SEQUENCE(20)-1,5)+1

If you want to start at another number play around with the -1. If you want more numbers change the 20.

 

[Fluff]

Maybe

+Fluff 1.xlsm
	A	B
1	31/08/2021	27/09/2021
2	31/08/2021	2
3	03/09/2021	2
4	06/09/2021	3
5	09/09/2021	3
6	12/09/2021	3
7	15/09/2021	4
8	18/09/2021	4
9	21/09/2021	5
10	24/09/2021	5
11	27/09/2021	1
12	30/09/2021	1
13	03/10/2021	1
14	06/10/2021	2
15	09/10/2021	2
16	12/10/2021	3
17	15/10/2021	3
18	18/10/2021	4
19	21/10/2021	4
20	24/10/2021	4
21	27/10/2021	5
22	30/10/2021	5
23	02/11/2021	1
24	05/11/2021	1
25	08/11/2021	2
26	11/11/2021	2
27	14/11/2021	2
28	17/11/2021	3
29	20/11/2021	3
30	23/11/2021	4
31	26/11/2021	4
32	29/11/2021	5
33	02/12/2021	5
34	05/12/2021	5
35	08/12/2021	1
36	11/12/2021	1
37	14/12/2021	2
38	17/12/2021	2
39	20/12/2021	3
40	23/12/2021	3
41	26/12/2021	3
Master

Cell Formulas
Range	Formula
A2:A41	A2	=SEQUENCE(40,,A1,3)
B2:B41	B2	=MOD(WEEKNUM(A2,2)-WEEKNUM($B$1,2),5)+1
Dynamic array formulas.

 

[BBxcl]

This will create a sequence of 20 numbers starting from 1 with the sequence 1,2,3,4,5,1,2,3,4,5 etc.

=MOD(SEQUENCE(20)-1,5)+1

If you want to start at another number play around with the -1. If you want more numbers change the 20.

Apologies, the question may not have been very clear.

I have a dataset with a date column that will continuously be added on to. For example a date of 04/10/2021 could be added which I’d need to know the week for. Since the weeks go from 27th Sep to 31st Oct from 1 to 5, in this case 04/10/2021 should be assigned the number 2. There can be multiple instances of 04/10/2021 and they are not always in sequential order.
I’m not sure if your solution would still apply there :/