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

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

Related articles