Advanced formula needed - Rostering statistics

So here is my problem.

I have a roster with over 50 people.
I have eight different start times over a 24 hour period (6am-2pm, 7am-3pm etc).
I WANT to be able to create a formula to tell me how many people are working at any given hour on any given day.
I have two columns i am working with. B column (dates) and G column (timings).
Using Win10 and latest Excel.

At the moment I am using a different roster program which does not support formulas. I can however transfer it to excel via .csv etc.
When I transfer the file it shows me numerous columns but as values only. I do have a date column and timings column.

So far I have attempted to create a COUNTIFS formula. That formula works great to tell me when people START at a given date and time but will not carry the value for an eight hour shift. = =COUNTIFS(B1:B2114,"02/02/19",G1:G2114,"*6a-2p*")
That formula is placed on a 24hour timeline similar to the below example.

This gave me a base stat to work from however; I must individually change each formula to suit each cell (dragging wont auto populate this one) which in reality, it is easier to manually count than to continue to change this formula individually for each cell.

I basically want to end up with a time line stating how many people are working at a specific time;

for example;

Shift workers starts at midnight, 2am & 4am. First would finish at 8am for example.

0000 0100 0200 0300 0400 0500 0600 0700 0800
1 1 2 2 3 3 3 3 2

Does anyone have a formula that would work? I am stumped on how to make this work, and make it feasible to use.

Thanks.

Hopefully this gives a better idea

Hi

You must convert your column 7 with this formula in a new column (H) to get the day time start work

Formula:

Please Login or Register  to view this content.

From C21 to Z21 you must use real time 00:00 to 23:00

In C23 use the following formula and drag forward and down

Formula:

Please Login or Register  to view this content.

Not needed but better, you can convert your column B to Excel real date.

See the file

Try in C3342

Formula:

Please Login or Register  to view this content.

That one is throwing out some huge numbers but I think it may be the sample i provided. I will try it against a proper roster.
The initial H conversion formula is making 12 midday appear as midnight.
Thank you for your ongoing support. It is coming along and is so close.

EDIT

got it....

I ended up running 3 columns,
A = date,
B= Shift times,
C= =IFERROR(IFERROR(LEFT(B1,2)+IF(MID(B1,3,1)="p",12,0),LEFT(B1,1)+IF(MID(B1,2,1)="p",12,0))/24,"")+A1

Then graphed next to it from E=

=COUNTIFS($C$1:$C$882,"<="&$E3+F$1,$C$1:$C$882,">="&$E3+F$1-8/24)

Thanks so much for all your help!

Highly appreciated