I few weeks ago I conducted a training session for a large manufacturing company in India and faced an interesting question during the training program.
I want you to take a look at the problem and try knocking it down
Overview of the Problem
The factory manager manually prepares the shift roster for all the employees. Due to that manual intervention he wastes a lot of time + there are a lot of errors. You have got to help him automate the shift allocation process
A few assumptions:
- Let’s say there are 7 employees
- There are 3 types of Shifts (A, B and C) for 8 hours each
- The employee can have any one weekly off (Monday to Sunday) but the off will not be given to him on the first day of the month
The conditions to keep in mind
- The Shift should change after the weekly off.
- For eg if the employee starts with shift A, after his weekly off his shift should change to Shift C and then again change to Shift B after the next weekly off
- The employee can start his month with any shift
- A, B or C
- But then the rotation of the shift has to be in this order --> A C B
- For instance if the month starts with shift C the next shift will be B and then A
The Outcome : Can you help the factory manager prepare a shift allocation schedule for the month of Jan 2017 ? To help you understand how the output should look like, take a look at the picture below (especially the cells in blue)
- For weekly offs I have used numbers (1 -7) where 1 means Monday and 7 means Sunday
- The allotted shifts in blue is the output
- Feel free to use helper formulas / dummy cells for calculations
Looking for more Excel Challenges
