Tags: department, employees, evening, excel, heading, hii, ise, microsoft, msdn, namely, pattern, programming, rota, shift, shifts, software, working

Programming for a Shift Rota Pattern

On Microsoft » Microsoft Excel

5,453 words with 2 Comments; publish: Tue, 10 Jun 2008 08:50:00 GMT; (30645.90, « »)

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?

All Comments

Leave a comment...

  • 2 Comments
    • 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

      "shriil" <sanjib.lahiri.excel.todaysummary.com.gmail.com> wrote in message news:1177935975.218405.148300.excel.todaysummary.com.c35g2000hsg.googlegr oups.com...

      > 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?

      >

      #1; Tue, 10 Jun 2008 08:51:00 GMT
    • Glad I'm not working that roster!

      On 30 Apr 2007 05:26:15 -0700, shriil <sanjib.lahiri.excel.todaysummary.com.gmail.com> wrote:

      >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?

      #2; Tue, 10 Jun 2008 08:52:00 GMT