Thread: 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!!!!