Formula to count employees on schedule over 24 hour period

Hi All,

I create a schedule of around 25 people in a 24 hour department. What I am trying to do is find the formula to automatically generate the chart under the schedule showing how many employees are on the schedule every 2 hours throughout the day. Currently this data is entered by counting the employees manually and typing it in.

I have attached a sample of the original schedule I use first, which I cannot seem to get to work because I have the starting time and ending time in the same cell (i.e. 10am-6pm).

Then I have attached a manipulated schedule, which divides the start time and end time into 2 columns, switches to army time, then uses the formula:

{=SUM(IF(A54>=B7:B31,IF(A54<C7:C31,1,0),0))}

This nearly achieves what I am trying to do, however, it trips up when the shift hits 12AM and the formula no longer holds true.

It seems simple enough, but I am missing something. Any help would be greatly appreciated!

Thanks,
Brijsek