distribute breaks for shift schedule RRS feed

  • Question

  • please i need some help, i have an employee shift schedule, that need to distribute the daily breaks within each interval shift but as per some conditions,

    as shown,I need fill “IN” at the interval cells that match its beside shift,
    From Row 14; each row considered an employee shift, in need to give every agent 1 hour as a break
    the build conditions:
    >> every agent must take 3 breaks the first: 15 minuts, the 2nd: 30 minuts, the last 15 minuts.
    >> we couldn't give a break at the first and the last hour at employee shift.
    >> at agent level: the duration between every break shouldn't less than 1 hour and shouldn't more than 3 hours,
    >> at interval level: at each interval break, the slots will be distributed as per SLA value at Row#:"10", change as per the given slots. The SLA value is a percentage value, i need the give breaks at the highest SLA value till this value equal the next lowest value then give slots to the next lowest value till equal the next lowest ect..., i need this operation to be looped till every employee take his 3 breaks as per the above conditions.

    the workbook

    appreciate any support, help to get an answer, thanks in advance,


    Friday, March 27, 2015 10:19 PM

All replies

  • ​Hi Ahmed,

    I have looked into the workbook, it is very complex. Could you split your requirement into several questions so that it will be easier for us to understand?

    >> every agent must take 3 breaks the first: 15 minuts, the 2nd: 30 minuts, the last 15 minuts.

    For this requirement, I think you could define a variable BreakCount, before you set the cell as break, you could check the value of the BreakCount, if it is less that 4, you could set the value of BreakCount +1. If it is larger than 4, break, and go to next. If you have any other conditions, you could add the if statement before you set the cell as break.

    Hope it will help.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 30, 2015 8:52 AM
  • Hello Edward,

    unfortunately, all conditions are must to meet the main goal, i'm not provisional neither in VBA nor using "solver", if it solvable via Solver,

    i need an applied code which can help me at this case.

    thanks a lot,      

    Monday, March 30, 2015 11:49 AM