Weekly rota with hourly breakdown query

Dear helpers,

I hope this is in the correct section.

I require the following and would appreciate any advice to help me produce this;

7 members of staff working across Mon - Thu (9am-8pm) and Fri - Sat (9am-5pm)

I will input their hours each week (as each week has a different pattern) i.e. Mon James 9-8, John 9-8 Jack 9-5 Joseph 5-8 and the so on for the rest of the week, I will write a daily timesheet

All I require is an hourly breakdown of how many staff I have in for each hour to forecast minimum staffing levels

2 is the absolute minimum, so 1 and below I would like to have highlighted in either red, or ideally, a yellow shaded cell

Lunch breaks are between 12-2pm, I would like to ensure that if I have 4 staff, 2 are on lunch at a time, and the same for 5 staff, 3 and 2, but more staff on duty for the 1pm-2pm slot if this is possible

Obviously I would tweak this for the daily worksheet, i.e. try to allocate the later lunch hour to 8pm staff is this is possible

Thank you for reading,
Sandy

If you've got 2010, then I'd run it by using COUNTIF to see how many of the timeslots cross each given hour.

Colors can be done like you want with conditional formatting.

Anyway see what the attached does for you.

Huge thanks Ben, this is excellent.

I have now completed this for a week.

Do you know how I could factor in lunch breaks for each member of staff that requires one? And balancing them, so all staff are not on the same one. They are between 12-1 and 1-2.

Generally only 9-5 and 9-8 staff will require them, I will edit these manually for any ad-hoc additions.

Thanks again,
Sandy