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!