Programming for a Shift Rota Pattern

[shriil]

Hi

I am heading a department which has 47 employees, working in Shifts,
namely Morning, Evening & Night Shifts. If Morning is M, Evening is
E , Night is N and Off day is O, my dept employess follow the
undermentioned 21 day cycle, which repeats itself for each employee

MMMEEEOMMEENNOMMMEENO in a relay fashion, i.e. if we consider the
First day of a month, Employee 1 may be doing duty as Per Point No. 5
of the 21-day cycle on that day, Employee 2 - Pt. No. 6 , Employee 3 -
Pt No. 8. On the second day, Employee 1 will be doing the duty as per
Point No. 6, Employee 2 - Pt. No. 7 Employee 3 - Pt. No.9, and so on
and so forth.

Now I need to bring out the Rota pattern of the employees for each
month. By running a macro I have been able to make a sheet with
Columns starting from the 1st day of month in question to the Last day
of the month along with Rows for each employee

Question 1:
Now for the employees, how do I define the positioning of each
Employee on each day, as per the pattern he is following within the 21-
day cycle as well as the relative positioning of other Employees on
that date and on the days that follow?

Question 2:
When I start with another month, how is it possible to know the Rota
Duty of an Employee on the first day of the month with respect to the
preceding duty he did on the last day of the previous month?

 

[Bernie Deitrick]

Enter the string MMMEEEOMMEENNOMMMEENO into a cell and name that cell Cycle.

Then, set up your table: Enter the date of the first day for each employee (as a date) into a cell for each employee - in this example, Column B, starting in row 2.

Name Cycle Start 1-May 2-May 3-May 4-May 5-May 6-May 7-May 8-May
Emp1 1-Apr E E N N O M M M
Emp2 5-Apr E O M M E E N N
Emp3 9-Apr M M E E E O M M


And so on, with dates of interest in row 1.

The formula in cell C2 is

=MID(Cycle,MOD(C$1-$B2,LEN(Cycle))+1,1)

which should be copied to all the other cells.


HTH,
Bernie
MS Excel MVP

 

[Kaye]

Glad I'm not working that roster!