how do i count how many people are working between two times in e

[Guest]

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?

 

[Bob Phillips]

Give this a try

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

--

HTH

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

 

[Guest]

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

 

[RagDyeR]

First of all, make sure that the data in Columns B & C, as well as B28 & C28
are *true* XL recognized times.

Secondly, the formula should be entered in a cell formatted as "General" or
"Number"!

The return you mentioned of
00:00
Is proof that the formula cell is *NOT* formatted correctly.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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

 

[Guest]

Thanks, that solved one problem and now at least it is counting. The problem
becomes if a shift starts at 20:00 on one day and ends at 8:00 on the
following day. The coloumns in B28 and C28 will only display 20:00 and 8:00.
If I want to know the number of people working between 8:00 and 14:00 it
will assume that the night workers are working during the day. Is there any
way to get round this without having to put dates into the rota?