Author: Oscar Cronquist Article last updated on December 11, 2018

This is a question I found at the bottom of this page Using Solver to schedule your workforce

Bank 24 processes checks 7 days a week. The number of workers needed each day to process checks is 17 workers are needed on Monday, 13 workers are needed on Tuesday, 15 workers are needed on Wednesday,17 workers are needed on Thursday, 9 workers are needed on Friday, 9 workers are needed on Saturday and 12 workers are needed on Sunday. All bank employees work five consecutive days.

Suppose our bank had 22 employees, and that their goal was to schedule employees so that they would have the maximum number of weekend days off. How should the workers be scheduled?

Here is the setup:

I created a new formula in C17. 

=SUM(H12:I12) + ENTER

All other formulas are created in the initial setup, see xls file found here: Using Solver to schedule your workforce

How to schedule employees so that they would have the maximum number of weekend days off

  1. Press with left mouse button on Data on the Ribbon
  2. Press with left mouse button on Solver
  3. Press with left mouse button on "Start Target Cell" button and select C17
  4. Press with left mouse button on Min in "Equal To:"
  5. Press with left mouse button on "By Changing Cells:" button and select A5:A11
  6. Press with left mouse button on Add button
  7. Press with left mouse button on "Cell Reference:" button and select A3
  8. Press with left mouse button on triangle and select "="
  9. Type 22 in "Constraint:" window
  10. Press with left mouse button on Add
  11. Press with left mouse button on "Cell Reference:" button and select A5:A11
  12. Press with left mouse button on triangle and select "integer"
  13. Press with left mouse button on Add
  14. Press with left mouse button on "Cell Reference:" button and select C12:I12
  15. Press with left mouse button on triangle and select ">="
  16. Press with left mouse button on "Constraint:" button and select C14:I14
  17. Press with left mouse button on OK!
  18. Press with left mouse button on Solve.

Get excel sample file for this article.
bank24_1.xls
(Excel 97-2003 Workbook *.xls)