How many employees at a certain hour

This might not resolve the issue, but I did notice a slight mistake in your formula (at least in the example you provided). Your formula in the 1st cell of each group starts out referencing row 3 instead of row 2 at the end of the formula.

The only thing I can see is that the times you want columns E and F too look for in B & C don't exist.

Those time ranges that return 0 don't have any equivalents in columns B and C and that is why you are getting 0 as a result. The earliest time range that you have is 6:00 AM to 7:00 AM. Anything referring to times earlier than that will return 0.

Thank you Ron for making what I was trying say much clearer.

@gmr4evr1
You're welcome. I think that there is a language problem here. The problem, I think, isn't well defined. There are time periods defined but nowhere do I see an employee count for the time periods unless the time periods are for each employee...I don't know.

IF, I understand (possibly I don't) the query then this solution will give a count of the 44 employees present during a given hour. I used a table populated with the formula:

=IF(ISNUMBER($B2),IF(ROUND($B2/(1/24),0)*(1/24)=ROUND(D$1/(1/24),0)*(1/24),"s",IF(ROUND($C2/(1/24),0)*(1/24)=ROUND(D$1/(1/24),0)*(1/24),"e",IF(OR(C2="d",C2="s",AND($B2>$C2,D$1<$C2)),"d",""))),"")

I then counted the values "s" and "d", assuming that an employee ends ("e") his/her shift at the beginning of the hour, using the formula:

=SUMPRODUCT((D$1:AA$1=AC2)*(D$2:AA$47="s"))+SUMPRODUCT((D$1:AA$1=AC2)*(D$2:AA$47="d"))

Here is a copy of your file with the table and formula applied: Copy of Libro1 (time ranges spanning midnight).xlsx
I imagine (hope even) that someone will post a one formula solution soon.
Let me know if you have any questions.

I still feel as if someone may come up with a simpler answer, however in the meantime you could move the 'helper' table and the tabulations so that it is all being handled without seeing the 'helper' table. As they often say on Food Network, presentation is everything. Here is the file again with the tables moved around and the helper table copied down to row 100 to give room for expanding the workforce: Copy of Libro1 (time ranges spanning midnight).xlsx
Let me know if you have any questions.

I found the cause, the first part of the OR function should have been ...AS3="d",AS3="s"... (a mistake I made when I moved the table) and have made the correction in the attached file: Copy of Libro1 (time ranges spanning midnight).xlsx
Let me know if you have any questions.

Your second formula seems to work very well with the exception of the fractional hours. Take a look at this array formula modification to your "Method 2" formula:

=SUMPRODUCT(MOD(1+(IFERROR(ROUND(B$2:B$47/(1/24),0)*(1/24),FALSE)<=E2)+(IFERROR(ROUND(C$2:C$47/(1/24),0)*(1/24),FALSE)>=(F2-"0:00:01"))+(E2>F2)+(IFERROR(ROUND(B$2:B$47/(1/24),0)*(1/24),FALSE)>IFERROR(ROUND(C$2:C$47/(1/24),0)*(1/24),FALSE)),2))

Once you paste it into H2 of the file attached to post #14 you'll need to press the F2 key then Ctrl, Shift and Enter simultaneously. You can then double click it down. Notice that it takes into account the two employees that sign in at 9:15 AM and out at 6:15 PM making them available from 9 - 10 but not from 6 - 7, and it still gives a correct count (7) of the employees available from 10:00 PM to 11:00 PM.
Let me know if you have any questions.