Counting People Working Each Hour

I am trying to count the number of people scheduled for each hour of the day. I have the formula below that works but when the shift starts with a time that is PM and ends with a time that is AM it will not count accurately. I often have shifts that start at 8pm and end at 2am. The formula below will not calculate properly in this instance.

Any help wtih this would be greatly appreciated.

=SUMPRODUCT(($B$29>=$D$5:$D$18)*($B$29<$E$5:$E$18))

=sumproduct(($d$5:$d$18<=$b$29)*(mod($e$5:$e$18-$d$5:$d$18,1)>=mod($e$5:$e$18-$b$29,1)))

Thanks for the fast response. When I put your formula in I dont get the result I am looking for. Maybe I am doing something incorrectly. I realize I didnt explain the sheet layout in the original post so I am attaching the file.

I need the coverage chart to count the number of people working each hour even when the shift starts wtih a time in the PM and ends in a time with the AM.

Thanks in advance for any help you can offer!

Template Drop Down with Coverage Chart.xls

That helps a little bit. Try =SUMPRODUCT(($D$5:$D$18<=B29)*(MOD($E$5:$E$18-$D$5:$D$18,1)>MOD(($E$5:$E$18-TIME(,,1))-B29,1)))

That works great except for the midnight hour. In the schedule if I enter a start time of 8pm and and end time of 2am I need to see the value of 1 in each cell of the coverage chart for 8p-9p, 9p-10p, 10p-11p, 11p-12a, 12a-1a, 1a-2a for that day.

So far everything I try stops short of calculating beyond the midnight hour.

Thanks again for any help you can offer!

Thanks Darkyam for your help. I think the issue was with the formatting of the times in column B. I deleted 12:00 AM from cell B49 and typed it in again. When I did this the formatting automatically changed to a custom format and then your formula started working. Now it works just like I need it too.

Thanks for your time, help and patience!!!!

Originally Posted by snapper

That works great except for the midnight hour. In the schedule if I enter a start time of 8pm and and end time of 2am I need to see the value of 1 in each cell of the coverage chart for 8p-9p, 9p-10p, 10p-11p, 11p-12a, 12a-1a, 1a-2a for that day.

So far everything I try stops short of calculating beyond the midnight hour.

Thanks again for any help you can offer!

Using this template as well. It is great except I am having this same issue. And the follow up formula posted does not seem to do anything.

Hello Darkyam,

Are you still a forum member? If you are I have a similar problem to your solution for Counting People working every hour. I am trying to count how many kids of a specific age group we have on an hourly basis from their sign-in and sign-out times.

Currently we have a column for their age (3 categories - under 2, 2-4yr olds and 5-6 yr olds) then a Sign-in time followed by a Sign-out time. We calculate, to the nearest 15 mins, how long they are in the centre, but now we need to provide information on how many kids of the three groups we have each hour during the day ie
9-10am, 10-11am, 11-12pm and so on till 5pm.