Rota helper??

Hi there,

I'm not sure if any of you excel whizzes can help me, but its worth a go. I have a very basic spreadsheet of a rota (attached). I hoping there is some sort of easier way of managing this, or even creating something brand new, rather than me counting up 1s based on the below criteria.

Any advise/assistance you can give me will be greatly appreciated! If its something that can't be done, thank you for taking the time to help anyway

Criteria:

Total hours to be covered 366 can not be any more.

Each person must work a minimum of 24 hours in total.

Person 5 and Person 6 - can't change

Hours in green also can't change

Each person can work minimum of 4 hours and a maximum of 14 hours (not sure if a formula can highlight this? )

For Person 1, 2, 3, 4, 7, 8, 9, 10 and 11 must always be two people.

The hours in orange and where i need the maximum number of people.

There is also a spare that can be used if enough hours that can be covered (24 hours)

Thanks in advance!

I know sorry I thought i'd just ask the question just in case there was a simple way, but it doesn't seem there is, so that's why its manual.

No breaks between shifts, as need cover and trying to give people the same hours where possible.

What do you mean by "hoping there is some sort of easier way of managing this"?

What is your goal? To minimise the number of people on Duty while still satisfying all your rules? Or maybe something else?

Hello there, well at the minute i manually just add in the "1" and try to give everyone around about the same hours. The goal is to make shifts more unified between shifts
rather than me manually adding in the number of hours people work - wasnt sure if there was some magical formula that could could just populate it for me.

For example Person 2, 3,4,7,8,9,10 giving them all a roughly the same hours based on the criteria e.g. So ideally, a single shift no longer than 14 hours, but no less than 6. no more than 366 hours total

Sorry bit of minefield.

There is no magical formula but code could be written to optimise your solution.

The thing is, for the same period and number of people, the answer will always be the same (as long as none of the rules change), so you only ever have to do it once.

Am I missing something?

It was worth an ask

It changes a lot sometimes might have 8 people, sometimes could be 40 people. There are then the peak times where as much cover is needed as
possible the times in orange.

Basically, its for lots of different events so it can range - when you have a lot of staff when your manually typing in 1s and trying to make the shifts
as fair as possible across everyone its, just labour intensive.

I was just seeing if anything at all could be done to help - for example maybe highlighting if I've put too many hours for someone in a period for example
shift not lasting more than 14 hours.

Just want to say, thank you though