I work for a large unit entertainment facility that has multiple departments and I run the Food & Beverage Department. We are very fast growing concept and are opening 10 new sites a year. We have have set staffing pars for hiring at these new sites but it seems we still have tremendous OT and at times trouble staffing shifts. I currently am in an existing site and before moving forward I wanted to run a trial on my waitstaff and our pars before trying to apply to a bigger site.

We currently use Hot Schedules which takes a servers Availabilty and splits into AM/PM - Available or Not Available (few have time constraints which we consider available). This has been exported to excel.

Here is the problem i have at max 147 shifts to fill a week or 1193 hours to fill a week. If each shift is eight hours how can i determine the correct staffing levels?

The issues i have come across is a server with open availability cannot work 14 shift only 5, and a sever that is available AM or PM can only work one or the other not both. So I am trying to accomplish is to build a formula that will read an available AM and PM shift for a server as 8 hours, either the AM or PM shift as 8 available hours, and represent accurate available hours while not allowing any Server to be available more than 40 hours.

Once I can determine the number of hours I have available for the staff versus the number of hours I need to fill on the schedule I can determine if I have reached an adequate staffing level.


I have tried using nested If formulas but I do not believe that is working well. Can someone please offer some solutions and guidance. I am not a novice but I am not an expert as many on this may be. I appreciate all the help

SERVER AVAILABILTY TEST.xls