Creating formulas that follow specific rules (7 day period)

[Jamie1994]

Hi all,

I've created a labour allocation and vessel planning workbook and I have managed to have it do everything I intended except for how it populates remaining people in a team after they are plotted in the workbook.

For example. If team 7 has 6 people in it and available for their 7 day period. They can either work a maximum of 5 day shifts or 4 night shifts. So currently my workbook says there are 6 people available for the full 7 days but I need it to recognise that depending on how many are used for days/nights reflects the next day and the next day for that 7 day period. With obvious factors such as you can't go straight from a night shift to a day shift etc. 

Could anyone assist with this. Cheers.

[#2]

You mentioned someone may work 5 day shifts or 4 night shifts. Can someone work a combination of day and night shifts? If so, we need to quantify days vs nights to draw up a formula.

[#3]

@Patrick2788 Hi Patrick.

Yes, so each individual can work a multiple of night shifts and day shifts however in one sequence each individual can only work a maximum of 5 day shifts of 4 night shifts. Also, if they work 2 day shifts and then off for 2 then they could work the last 3. Similarly they cannot work nights on the final 7th day due to the way in which the rota operate. 

My workbook already gives the value of how many are available in the team and takes holiday and sickness into account. I just need it to now populate how many are available as the 7 day period transpires.

Regards,

Jamie