Creating a formulated schedule for retail stores - NEED ASAP

amoleski

New Member
Joined
Jul 30, 2018
Messages
1
Hi Everyone,

I could really use some help with my formulas- and I have searched google with no luck. So here it goes

Backstory/reason- So I am currently putting together a formula driven retail scheduling report in excel for my company's 10 retail stores. These scheduling reports are broken out by hour for a single day. The best part about it is that we currently know exactly how many hours we need for that day. We have roughly 26-37 team member slots available (although may not use all of them). In order to make this process super easy for the stores, we want to provide them a shaded chart that shows the work shifts, and they will just enter the team members name in the field. No guess work for our store managers.

Here is where I am getting stuck. I essentially want it to work that once the hours are determined for the day, there is a formula that generates the most efficient scheduled hours (no more than 8 hours a day - with a 30 min break for anyone over 7 hours) directly on the schedule. Once you change the total number of hours for the day, the formula should change with it.

What I have so far... My main formula is =IF(OR(SUM(J$18:J19)<=(J$59-1),I20=1)*AND(SUM($H20:I20)<=(($AJ20*2)-1)),1,0)

What I can't have: I CAN'T have anyone working less than 4 hours, I CAN'T have anyone over 8 hours (excluding 30 min lunch). I CAN'T have the scheduled hours be over the hours needed for the hour (1 hour is fine, but it should be equal to the hours needed).

Problem: I have 21 hours scheduled when I only need 12. This could be fixed manually, but the object is to have excel realize that as long as there are 4 hours, they can skip that person, or provide a lunch break during that time, that will drop the hours down to another teammate.

Example: Team member 3, 4 & 5 all have 5 hours worked. However they could all end their shift at 12:30 (4 hours), which would cause my scheduled hours to decrease.

ANY HELP would be AMAZING! I have tried scheduling tools in excel, looking it up on google, and my brain is working overtime

**I don't know how to upload pictures of my workbook so advice on how to do that would be great, and probably easier**
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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