I am trying to prepare a rota for my carers in a nursing home on Excel. We
have carers working 24 hours a day in the nursing home but the number of
carers vary according to the number of residents that we are caring for and
the time of day.

I would like to count on my rota the number of carers working between two
times. I have set up my worksheet so that, for instance, the start time of
each carers shift is displayed between cells B6:B26 and the end time between
cells c6:c26 for each respective carer.

I want the spread sheet to count the number of carers working between the
start time entered in cells b28 and the end time entered in cell c28. Note
that night carers may work, for instance, between 8 pm on one day and 8 am on
the following day.

I have tried entering the formula
=COUNTIF(B6:B26,"=b28")-COUNTIF(C6:C26,"<=c28") but it does not seem to work
for any time of day. Anyone have the answer to the formula I would use?