I have posted a question on here before relating to this issue but I can not
now find my post so you will forgive me if you are re-reading this.

I am trying to create a rota for my staff who work 24 hours a day, 7 days a
week. I do not want to include dates on the rota (due to the size of the
spreadsheet I want to have).

Cells A6:A21 contain the names of the staff. Cells B6:B21 contain the start
times for each respective member of staff on a Monday and similarly cells
C6:C21 contain the end times on a Monday. The rota uses the 24 hour clock
but like I stated earlier, no dates.

I want to be able to count the number of staff who are working between two
selected times. The start time to be counted is selected in cell b24 and the
end time in cell c24. I have worked out the following formula which halfway
gives me the answer I want:

=IF(B24C24, SUMPRODUCT(--(B6:B21=B24), --(C6:C21<=(C24+1))),
SUMPRODUCT(--(B6:B21=B24), --(C6:C21<=C24)))

The problem occurs if staff work over-night. Say someone starts work at
20:00 and ends work the following day at 8:00 then that person should only be
counted if the first part of the array above is "TRUE" and not otherwise.
The solution would be to add 1 to the end time of anyone who is working
overnight (in accordance with http://www.cpearson.com/excel/datearith.htm)
but how would I do that in the above formula?