ROSTA problem

I posted this question in the Excel Formulas & Functions Forum and very quickly was given a possible solution however, given the amount of data I need to calculate the solution was going to take too long, I have tried myself to work out how to automate the given solution and asked again in the above forum for help but fear that the thread has long since vanished from anyones attention. I also suspect that it's really a programming/vba solution I'm looking for, so I'm asking in this forum if anyone has come across this issue before and maybe has an off the shelf soultion...thanks in advance.


I run a care home and have until now run a traditional rota of Early. Late and Waking Night shifts. These shifts all start at set times, i.e. an "Early" shift starts at 8 and finishes at 3.30, a "Late" starts at 2.30 and ends at 10.00. These are shift patterns common to many care homes all over the place and are largely based on the needs of the staff rather than the particular needs of the residents. With the move to more person centred services I have created a spreadsheet that looks at the support plans we have in place for our residents and generates a half hourly breakdown of exactly how many staff I need on shift for each day of the year. (See attached sheet)

My problem is how to translate the information on it into an efficient shift pattern. I am not aiming for complete needs led services here, I still have to give my staff some consistency and work/life ballance but would like to be able to meet the needs of my residents by introducing shifts that start and finish at different times, thus not having 7 staff on an early at 8.30 when we only need 3 in the building until 10:00 Conversly not having only 4 staff in the building at 19:30 when we need 6.

I can present the information in two ways, either a single row of numbers underneath the times of the day indicating total number of staff required for each half hour, or several rows of 1's indicating cumulative staffing requirement throughout the day. In the example sheet I have shown that we require 2 staff for the night shift from midnight to 08:30, the evening night shift starts at 21:30 to midnight. Between these times I'd like the sheet to automatically rota staff on 7.5 hour shifts as efficiently as possible to cover the requirements. I appreciate it probably will be impossible to completely meet the need but if I need to have a member of staff on a 7.5 hour shift to cover 1 hour of support then I can live with that.

Anyway, hope that is all clear and any help much appreciated.