Share [Social9_Share]

Allot the correct shift – Excel Challenge

allot-the-correct-shift-excel-challenge

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:

  1. Let’s say there are 7 employees
  2. There are 3 types of Shifts (A, B and C) for 8 hours each
  3. 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

  1. 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
  2. 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)

allot-the-correct-shift-excel-challenge-2

  1. For weekly offs I have used numbers (1 -7) where 1 means Monday and 7 means Sunday
  2. The allotted shifts in blue is the output
  3. Feel free to use helper formulas / dummy cells for calculations

 

DOWNLOAD THE SPREADSHEET HERE

 

Looking for more Excel Challenges

  1. Find the Total Hours Challenge
  2. Shift Timing Challenge

 

  • EthernetCable

    In cell E5 I have this rather grim looking formula. I’ve separated each statement with a line break to make it easier to read

    =IF(E$4=MIN($4:$4),$C5,
    IF(VLOOKUP($D5,$B$15:$C$21,2,FALSE)=TEXT(E$4,”dddd”),”Off”,
    IF(D5″Off”,D5,
    IF(C5=”A”,”C”,
    IF(C5=”C”,”B”,
    IF(C5=”B”,”A”))))))

    • Abbas Bakhrani

      My 2 Cents
      Good formula, but it is static. What if the dynamics of shifts change? You would need to change the formula to change the shifts “A”, “B”, and “C”
      I would consider you use helper table / named range for shifts and use in the formula

      • EthernetCable

        Agreed. Assuming I have the same shift rotation table as in your answer then:

        =IF(DAY(E$4)=1,$C5,
        IF(VLOOKUP($D5,$B$15:$C$21,2,FALSE)=TEXT(E$4,”dddd”),”Off”,
        IF(D5″Off”,D5,
        INDEX(Shift,MOD(MATCH(C5,Shift,0),ROWS(Shift))+1))))

        I prefer using MOD() to handle the shift3 > shift1 change rather than capturing it directly as in your solution. I simplified the first IF() statement as well.

        • Abbas Bakhrani

          Nice variation

  • GraH

    I added some helper columns/rows:
    – In Row 2 as of Cell E2: = WEEKDAY(E4,2) => weekday number
    – In Column A as of Cell A5: =FIND(C5,”ABC”,1) => shift position
    – In Range F15:H17 I added the shift sequences and named it SHIFTS
    A B C (in row 15) ; B C A (in row 16) ; C A B (in row 17)
    The result being then:
    =IF(E$2=$D5,”OFF”,INDEX(SHIFTS,$A5,MOD(COUNTIF($E$2:E$2,$D5),3)+1))
    – If the Weekly Off equals the weekday number then “OFF”
    – The index matches the Shift Position (1-3 for rows) and
    – mod returns the column number where
    – countif returns the cumulated number of OFF days
    – divided by the number of shifts (here 3) => I know I hard coded. 🙂
    – I add one, to solve the issue when mod is 0, I actually need an index 1 and so on.

  • Abbas Bakhrani

    First I put a helper row above the short day name, and used the formula =DAY(E$4) to get the day number, then filled the row

    Then I changed the range for Off Number & Day to table and used the formula on Cell B15 and filled down
    =IF(ISNUMBER($B14),$B14+1,1)

    Then I created a helper table called shifts as per the image below, and used the formula and filled it down
    =IF(ISNUMBER($E14),$E14+1,1)

    Then I used the following formula on Cell E5 and filled it to the last cell

    =IF(E$2=1,$C5,
    IF(E$3=LEFT(INDEX(Day,MATCH($D5,OFF_Number,0)),3),”Off”,
    IF(D5″Off”,D5,
    IF(INDEX(Shift_Num,MATCH(C5,Shift,0))=MAX(Shift_Num),INDEX(Shift,MATCH(MIN(Shift_Num),Shift_Num,0)),
    INDEX(Shift,MATCH(INDEX(Shift_Num,MATCH(C5,Shift,0))+1,Shift_Num,0))))))

    I used the following named ranges in the above formula
    Day
    OFF_Number
    Shift
    Shift_Num
    https://uploads.disquscdn.com/images/0b3cb5ab3c5a73b6c6e13629341c94ceaa670cd1c159fd7485c97cb1d577ed81.png

  • Raja Simon

    I used helper Cells. However, my formula can be used to increase the number of shifts dynamically.

    =IF(DAY(E$5)=1,$C6,IF($D6=MOD(E$5-1,7),”OFF”,IF(D6=”OFF”,HLOOKUP(MOD(HLOOKUP($C6,$F$19:$I$20,2,0)+1,3),$F$20:$I$21,2,0),D6))) https://uploads.disquscdn.com/images/538d4bc949ff498de2a060c2c821820b6d3dcd4c54a62f3a38a5965f50898437.jpg

  • GraH

    Oh, I just read the last assumption: the off day may not fall on the first day of the month. And the rotation is in a reversed order (C B A). Completely overlooked both conditions. Back to the formula bar I guess.

Chandeep

Popular Posts

© Goodly