I am trying to solve an optimization problem on Excel or Python. My constraints are the available number of employees (=20), the number of working days/month (=20), the number of working hours/day (=8), and the maximum number of overtime working hours/day (=6). The variable I'm trying to minimize is cost: employees get USD 20/hour for work <=8 hours, USD 30/hour for work >8 and <=12 hours, USD 50/hour for >12 and <=14 hours.

The jobs require a set number of hours and are bound by start and end dates.

  1. Job 1: Start 08/01/2022, End 09/01/2022, Hours 200
  2. Job 2: Start 08/15/2022, End 09/15/2022, Hours 250
  3. Job 3: Start 08/30/2022, End 09/15/2022, Hours 100
  4. Job 4: Start 08/01/2022, End 09/15/2022, Hours 300

How do I go about this? All the case studies I see online are for much simpler problems with not as many constraints and/or not bound by start/end dates.

  • $\begingroup$ I think, you'll need to model this by work done per day. That also means a calendar is involved (you did not specify what is the rules are for the weekends). A simplified model could just minimize the maximum workload for each day. That would automatically minimize overtime. $\endgroup$ Aug 4 at 10:09
  • $\begingroup$ Thanks a lot for the response Erwin! So no work is allowed in the weekends, hence why I specified 20 working days/month. When you say "model this by work done per day," what exactly do you mean? $\endgroup$ Aug 4 at 10:24
  • $\begingroup$ I would probably start with variables work(j,t)=hours spent on behave of job j during day t and totwork(t) = total hours worked on day t. With these definitions, the equations should follow more or less automatically. $\endgroup$ Aug 4 at 11:24
  • $\begingroup$ Once you have the basic model, you will want to split your hours worked variable(s) into three variables: hours worked at base rate (maximum 8 per day); hours worked at time-and-a-half (maximum 4 per day); and hours worked at the highest rate (maximum 2 per day). The constraints that each rate starts only after the previous rate was exhausted need not be made explicit. The solver will automatically choose a solution satisfying that since it will be cheaper than a solution that works overtime before exhausting regular time. $\endgroup$
    – prubin
    Aug 4 at 15:21
  • $\begingroup$ Ended up doing it manually--was far too complicated to be done on Excel Solver. Thanks a lot everyone for your help! $\endgroup$ Aug 7 at 14:25


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.