headcount by hour spanning midnight

[rish251974]

Hello, I am looking for a formula/method to calculate the headcount by hour of day. I have employees whose shift crosses midnight hours.

e.g.

employee Start End
A 06:00 14:30
B 14:00 22:30
C 22:00 06:30

Now I would like to populate a sheet with hours of the day and a column that displays the headcount by hour

Hr Headcount
1 1
2 1
6 2

For some reason, when I used the sumproduct formula, it didn't give me the right count for employees whose shift crosses midnight.
Any help would be greatly appreciated.

Thanks.

 

[rish251974]

Hello,
Here is an example of the data :

[TABLE="width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]start time[/TD]
[TD]end time[/TD]
[/TR]
[TR]
[TD]2349998[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]14:30[/TD]
[/TR]
[TR]
[TD]2078478[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]6:30[/TD]
[/TR]
[TR]
[TD]1041140[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]14:30[/TD]
[/TR]
[TR]
[TD]2058294[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]6:30[/TD]
[/TR]
[TR]
[TD]1034752[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]14:30[/TD]
[/TR]
[TR]
[TD]411379[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]14:30[/TD]
[/TR]
</tbody>[/TABLE]

This is what I want to display
Hour Headcount
1 2
2 2
3 2
4 2
5 2
6 6
7 4
8 4
9 4
10 4
11 4
12 4
13 4
14 4
15 4
16 0
17
18
19
20
21
22
23
24