Predicting working hours available, allowing for half hour starts...

[lever.mark]

Hi,

I am trying to make a spreadsheet that works out the number of employee's available per hour.

I then use the data to predict the amount of work we can perform each hour, and therefore i can track whether we are ahead or behind schedule over the course of the day.

I have managed to do this partially and have attached a sample spreadsheet.

I can get the sheet to count up and multiply for full hours, i.e count how many people are here from 07:00-08:00, and this works if they start at 07:00. What i now need is for it to account for people starting at 07:30 and add 0.5 into the table. The only adjustment is for 17:00-18:00 where the work we can perform is halved due to end of day routines. I have allowed for this with a /2 added into the Hours available column for that row.

Another Problem is that the current method doesn't appear to count the employees available 17:00-18:00 correctly.

I am also trying to remove breaks from the schedule and having the same issue with only subtracting those people who's breaks begin on the hour.

I have supplied the "correct" answers i'm looking for below the table's i currently have working.

Hopefully that made enough sense for someone to help me out!

[lever.mark]

I have had a play, and have managed to get it partially working to what i would like.

One issue, is that although the function works for most time, it doesn't appear to work for times involving 15:00, or the last hour up to 18:00.

Those staff that finish at 15:00 are still being counted until 15:30. Similarly those that finish at 17:30 are still being counted until 18:00.

I have attached the new sheet, any idea's?