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 :

ID	start time	end time
2349998	6:00	14:30
2078478	22:00	6:30
1041140	6:00	14:30
2058294	22:00	6:30
1034752	6:00	14:30
411379	6:00	14:30

<colgroup><col span="3"></colgroup><tbody>
</tbody>

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