Assigning recurring weeks that reset every month

BBxcl

New Member
Joined
Sep 29, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
Maybe
+Fluff 1.xlsm
AB
131/08/202127/09/2021
231/08/20212
303/09/20212
406/09/20213
509/09/20213
612/09/20213
715/09/20214
818/09/20214
921/09/20215
1024/09/20215
1127/09/20211
1230/09/20211
1303/10/20211
1406/10/20212
1509/10/20212
1612/10/20213
1715/10/20213
1818/10/20214
1921/10/20214
2024/10/20214
2127/10/20215
2230/10/20215
2302/11/20211
2405/11/20211
2508/11/20212
2611/11/20212
2714/11/20212
2817/11/20213
2920/11/20213
3023/11/20214
3126/11/20214
3229/11/20215
3302/12/20215
3405/12/20215
3508/12/20211
3611/12/20211
3714/12/20212
3817/12/20212
3920/12/20213
4023/12/20213
4126/12/20213
Master
Cell Formulas
RangeFormula
A2:A41A2=SEQUENCE(40,,A1,3)
B2:B41B2=MOD(WEEKNUM(A2,2)-WEEKNUM($B$1,2),5)+1
Dynamic array formulas.
 
Upvote 0
Solution
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 :/
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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
Back
Top