Thanks for the help. Firstly when I first looked at your reply the formula
was displayed as ("formula 1"):

=SUMPRODUCT(--(B6:B27<=B28),--(C6:C27=C28))

Now trying to reply to you, I noted it is displayed as ("formula 2"):

=SUMPRODUCT(--(B6:B27<=B28),--(C6:C27=C28))

In Formula one excel displayed and error and the "lt" was highlighted.

Formula two was accepted without an error but when I tested it with some
times in cells b6 & c6 it still showed 00:00.

Thanks anyway


"Bob Phillips" wrote:

Give this a try

=SUMPRODUCT(--(B6:B27<=B28),--(C6:C27=C28))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"APYDS" wrote in message
...
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?