Optimized scheduling to demand based on constraints

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
The title kind of states my goal. I am looking for some help to get started to solve the problem of scheduling a group of people based on demand and their individual constraints, using solver and VBA (or maybe a combo of the two?)

Here is a picture to describe what I am looking for help to create via solver/vba:

ABCDEFGHIJKLMNOPQR
1Time of Day9:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM4:00 PM5:00 PM6:00 PM7:00 PM8:00 PM9:00 PM10:00 PM
2unavailable Total Zone Demand/Hr56569111013151813951126
3FTEmp. 111001111118FT Hr. Contraints
4After 7PMFTEmp. 211101111119MinMax
5FTEmp. 3110111111869
6FTEmp. 4110111117PT Hr. Constraints
7FTEmp. 5110111117MinMax
8FTEmp. 6111011111846
9FTEmp. 71110111118
10FTEmp. 81111011118
11FTEmp. 911011116
12FTEmp. 1011111001118
13After 2PMPTEmp. 1111111 5
14After 3PMPTEmp. 1211114
15PTEmp. 1311114
16PTEmp. 14111115
17PTEmp. 1511114
18PTEmp. 1611114
19PTEmp. 171111116
20PTEmp. 18111115
21FTEmp. 1911114
22PTEmp. 2011114
23FTEmp. 2111114
24PTEmp. 220
25FTEmp. 230
26Scheduled56569111013151813951126
27Variance000000000000000

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2676;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:791;width:17pt" width="23"> <col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:2443;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:3072;width:66pt" width="88"> <col style="width:48pt" width="64" span="15"> </colgroup><tbody>
</tbody>

Row 2 has the demand needed per hour (totals 126 hours for the day).
For each employee throughout the day, I use a 1 or 0 to represent the shift work time and lunch (1 is an hour worked and 0 is lunch).
Row 26 is the total of the 1's (hours worked).
Rows 27 is the variance row 2 and row 26.
Column A states when a person is unavailable.
Off to the right side you will see full time employees (FT) need to work between 6 and 9 hours a shift with either a 30 minute or 1 hours lunch, and PT employees need to work between 4 and 6 hours per shift (no lunch).

The part I am looking for help on is to use solver/vba to fill in cells D3:Q25 by using all the hours available (in this case 126 hours), with the least absolute variance total in row 27. My example shows 0 variance but many times there is a small absolute variance.

Thanks in advance for any help or direction.

SD
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
Hello everyone. Bumping this back up. I know it's complex. Does anyone have ideas? Happy to explain further if needed.

THanks!
SD
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
Hello everyone. Bumping this back up. I know it's complex. Does anyone have ideas? Happy to explain further if needed.

THanks!
SD


These get buried quickly. Bumping again to find some Excel MVPs that might be able to help.

SD
 

sdoppke

Well-known Member
Joined
Jun 10, 2010
Messages
647
These get buried quickly. Bumping again to find some Excel MVPs that might be able to help.

SD
Hello - still looking for help from the experts. Any ideas, or direction?

Thanks a ton in advance!
 

Forum statistics

Threads
1,081,990
Messages
5,362,584
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top