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.


Week One


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


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!

1 Answer 1



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

Empty table

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:


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


Numbers in the table

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)



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.