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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hello everyone. Bumping this back up. I know it's complex. Does anyone have ideas? Happy to explain further if needed.

THanks!
SD
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top