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**
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**
