# Minimize cost given working days/hours/required hours per job/start & end dates

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.

• 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. Aug 4 at 10:09
• 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? Aug 4 at 10:24
• 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. Aug 4 at 11:24
• 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.
– prubin
Aug 4 at 15:21
• Ended up doing it manually--was far too complicated to be done on Excel Solver. Thanks a lot everyone for your help! Aug 7 at 14:25