• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rotation duty roster

C

Cinta B

New Member
Hi,

I am wondering if there is a way to create a rotation duty roster on Excel, a monthly one with different shifts and work hours. A worker cannot exceed 19.5 hours/week, and the shifts would be different every month. Any idea and help would be grateful, thanks. The idea is that every month, one will just input the name of workers, and automatically the hours added for each worker would be less than 19.5 hrs/week.
 

Attachments

  • shifts.xlsx
    11.3 KB · Views: 19
Hi ,

1. Please separate the input data from the output. If it is possible keep them on separate worksheets.

2. Describe all the possible constants , constraints and rules.

I assume the data in the range A1:D7 is constant (unvariable).

I assume the two constraints are :
  • no one can exceed 19.5 hours/week
  • the shifts would be different every month
How are you going to enforce the second constraint ? Will previous data be stored in the same workbook ?

3. No rules have been specified ; if there are any , please specify them.

Narayan
 
Hi Cinta B,
I'm also wondering if you are still in conceptual mode.
My wife asked me this morning to take a look at the manual Excel planning she uses for her teams. I just spend a few minutes looking for any requirements in there. I have already listed about 20 "rules" to implement in a more automated version. You mention only 2 or 3 rules.
So how far do you want to go with your idea? Depending on that, some things can be possible in Excel, but it could also be that other tools might be a better option (like access).
The point is not to scare you away, but I want to make sure precious time is not wasted on finding solution that would not be used/useful.
 
That's fine, Cinta.
Viewing your file, some questions did come to mind.
  • So shifts 4 and 5 are only for Saturdays?
  • Each day needs to cover all shifts from 8:45 to 21:00, except Saturdays & Sundays?
  • Saturday is always done by 1 person, Sundays by 2 and weekdays by 3 persons?
  • What does it mean shifts are different per month? As in not the same pattern? like: "Shift 1, shift 1, no work, shift 1, shift 2 , no work, no work" would be pattern for Cindy in month 1, so the next month this pattern may not be used?
  • In the same month the same pattern may be used (or must?).
  • If I understand correctly, some-one is entering the names manually. Are changes allowed?
  • Do you need to consider official holidays, illness and other leaves?
 
Hey GraH,

Thanks for your reply and I apologize for the late reply because I am so occupied with projects, homework, and deadline.

Shifts 1-3, Monday - Friday
Shift 4 - Sat
Shift 5 and 6 - Sunday

The project is a challenge and I am using a combination of "combo box", some rules like conditional formatting, sumproduct, concatenate, IF function.
Somedays the "desk" would be left attended because all workers are assigned with their appropriate weekly hours that should not exceed 19.5/Hrs. The goal is that the working pattern should not be the same for 2 months, (if I can show it!), and then the working shifts can be recycled again. And yes, when someone enters a new shift for a new worker, everything is automated and calculated.

For this project, officical holidays, leaves, illness are not important. That would be in the next level!

I will share you my file in a bit. Got to run.

Thanks.
 
You must log in or register to reply here.
Back
Top