# Rotating Shift by Week

I am attempting to create an auto-rotating shift as follows:

There are 9 employees.

The shifts are:

Tuesday 12 - 2 in half an hour blocks (12, 12:30, 1:00, 1:30) Thursday 11:30 - 2:00 in half an hour blocks (11:30, 12, 12:30, 1:00, 1:30)

The last person to work on Thursday should be the first person to work on Tuesday of the next week.

Ex.

Week One

``````           11:30-A
``````

12:00-L 12:00-B 12:30-LD 12:30-D 1:00-M 1:00-H 1:30 R 1:30-K

Week Two

``````           11:30-R
``````

12:00-K 12:00-A 12:30-L 12:30-B 1:00-LD 1:00-D 1:30 M 1:30-H

I am not sure how to code that. Any help would be great!

Welcome!

Let's solve this problem together step by step.

First of all, create a list of employees.

The shift schedule will be placed in a rectangular table. Get something like this Let's say we renumber our employees from 0 to 8 (yes, you can renumber from 1 to 9, but the formula will get a little more complicated because of this)

And now we get the number of the employee, depending on which row and which column he is on duty:

``````=MOD((COLUMN()-COLUMN(\$Cell_of_the_very_first_block))*8+ROW()-ROW(\$Cell_of_the_very_first_block),9)
``````

In my example, the cell of the very first block is located at E5, so the formula will be:

``````=MOD((COLUMN()-COLUMN(\$E\$5))*8+ROW()-ROW(\$E\$5),9)
`````` Look at the numbers week by week. You see? This seems to be the correct order of shifts. Now, using the OFFSET() function, let's turn these numbers into employee names:

``````=OFFSET(\$A\$5, MOD((COLUMN()-COLUMN(\$E\$5))*8+ROW()-ROW(\$E\$5),9),0)
`````` • This worked amazingly! Thanks so much for the help! Aug 30 at 16:18