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)

Weekly Blog EMAIL

[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Extract unique distinct values if value contains string

The image above demonstrates a formula in cell F3 that extracts unique distinct values from column B if they contain […]

Identify numbers in sum using Excel solver

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]

Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

Convert date ranges into dates

The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

Advanced Gantt Chart Template

This Gantt chart uses a stacked bar chart to display the tasks and their corresponding date ranges. Completed days are […]

Count weekday within date range except holidays

Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]