Count how many staff per hour

Hi

I need to work out how many staff I have on site per 15 minutes, problem i an having is when the clock ticks over midnight.
No need to worry about breaks as my staff are still on site.

So I have in column B start times, column C finish times and column F times running every 15 minutes: 07:00, 07:15 and so on.

In column G in line with column F times I have my formula: =SUMPRODUCT(($B$7:$B$20<=F18)*(MOD($C$7:$C$20-$B$7:$B$20,1)>MOD(($C$7:$C$20-TIME(,,1))-F18,1)))

Work great until midnight then nothing.

Any help here would be great.

Edit for now is all seems to be fine except I am loosing the last 15 minutes of the shift.

Thanks

Welcome! Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Originally Posted by bobforde

Edit for now is all seems to be fine except I am loosing the last 15 minutes of the shift.

It looks as if the head count looses the first 15 minutes of the shift. Employee 2 works from 6:00 AM to 7:00 AM. The head count has a zero for 6:00 AM, which looks as if it is counting the employees present from 6:00 to 6:15. When you look at the actual values being evaluated you see that the value in B8 is 0.25 and the value in F2 is 0.249999... To account for that you could round the values to x number of decimal places (i.e. six).
The zero in G2, head count for 7:00 to 7:15, seems appropriate as no employee is present between 7:00 AM and 10:00 AM.
Try pasting the following formula in G2 and copy down:

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.