determine Shift by week number

[salahmost]

We've turned to 3 shifts instead of two.

I have 3 groups of people who change shifts regularly, Group A - Group B - Group C , groups swap shifts with each other So they will go forward 1 shift.
week1: A B C
Week2: C A B
week3: B C A ... then again A B C....etc.
.I would like something that gives me a visible display in Excel when I change a week number box, so it shows me what shift they are on for that week number or for any data.
I use the next formula to calculates the week number of the Date base on Week 1 beginning on the first Saturday of the year as week start day is Saturday and week end data is Thursday and Friday is our off day:

=TRUNC(((B2-DATE(YEAR(B2),1,1)+MOD(7-WEEKDAY(DATE(YEAR(B2),1,1)),7))+6)/7)

Where B2 contains the date.
Thanks in advanced.

ـــــــــــــــــــــــــــــــــــــــــ
ٍSalah

 

[DonkeyOte]

I'm not totally sure I follow but if we say A1 holds Group and B1 the Week Number then

C1: =3-MOD(-G1-(MATCH(F1,{"A","B","C"})-1),3)

would give you the shift for that combination be it 1, 2 or 3.

(I'm sure there's a much more succinct formula)

 

[DonkeyOte]

I think the below would perhaps be more logical... ?

C1: =1+MOD($B1-1+MATCH($A1,{"A","B","C"})-1,3)

the prior example has incorrect references - oversight on my part when testing - F1 would be A1 (the Group letter) and G1 would be B1 (ie the week number)

 

[salahmost]

Worked like a charm!
Thank you so much.

ــــــــــــــــــــــــــــــــــــــــــــــــــــ
Salah

 

[salahmost]

For expected new group that will work 2 shifts A and C ... How can we modify the MOD function?