Week 1 = A, Week 2 = B, etc

[channdy]

Hi guys,
I have alot of data and we run a 4 week rotating roster. (A, B, C, D)
Does anyone know how to assign these using either a date or week of year field?
i.e.

Date (I have)	Week of year (I have)	Week of roster (I need!)
02/01/2024	1	B
26/08/2024	35	B
30/12/2024	53	D

Many thanks in advance for your help!

 

[Peter_SSs]

Welcome to the MrExcel board!

I don't understand your sample results since there you have Week 1 as B yet in your thread title it says Week 1 = A

See if this (in the top cell of the results column) does what you want. Adjust the range to suit your actual data.

24 08 29.xlsm
	B	C
1	Week	Roster
2	1	A
3	35	C
4	53	A
Roster

Cell Formulas
Range	Formula
C2:C4	C2	=MID("ABCD",MOD(B2:B4-1,4)+1,1)
Dynamic array formulas.

 

[channdy]

Welcome to the MrExcel board!

I don't understand your sample results since there you have Week 1 as B yet in your thread title it says Week 1 = A

See if this (in the top cell of the results column) does what you want. Adjust the range to suit your actual data.

24 08 29.xlsm
	B	C
1	Week	Roster
2	1	A
3	35	C
4	53	A
Roster

Cell Formulas
Range	Formula
C2:C4	C2	=MID("ABCD",MOD(B2:B4-1,4)+1,1)
Dynamic array formulas.

IT WORKED!!! Thank you so much
Title was just example- Table had correct weeks I used your formula and changed it to "BCDA" and it worked perfectly for 12000 odd lines.
Genius thank you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up. Glad you were able to adjust.