Thanks for trying Farrell but that wont solve the problem. What happens if I
wanted to know all the day staff who worked between 14:00 and 18:00. And
what would happen if I wanted to know how many staff worked between 20:00 and
23:00? I appreciate that your formula is based on what you feel is a likely
occurance in staff working pattern but unfortunately with my staff nothing is
likely.

I am grateful for your efforts though and if you have any more ideas please
let me know.

Regards

Apyds


"Art Farrell" wrote:

> Hi,
>
> I may have the wrong slant on what you are trying to achieve, but I would
> change two places in your formula:
>
> Original: =IF(B24>C24, SUMPRODUCT(--(B6:B21>=B24), --(C6:C21<=(C24+1))),
> SUMPRODUCT(--(B6:B21>=B24), --(C6:C21<=C24)))
>
>
> Revised: =IF(B24>C24,SUMPRODUCT(--(B6:B21>=B24),--(C6:C21>=C24)),
> SUMPRODUCT(--(B6:B21>=B24),--(C6:C21>=C24)))
>
> In your example with 20:00 hours as the start and 8:00 hours as the finish
> of the shift if you use C24+1 that means every time value in your C range
> has 24 hours added to it and all those inputs will give a TRUE output in the
> range comparison. The reason I changed it to greater than is if a person
> started at 22:00 he's most likely to have a 12 hour shift ending at 10:00
> and he will be added to the total. With the less than operator a person
> could start at 23:00 and finish at 1:00 and be added in.
>
> My reasoning would be the same for B24<C24 so the operator here should be
> the same: >=C24.
>
> And then,maybe I misinterpreted your problem.
>
> CHORDially,
> Art Farrell
>
>
> "APYDS" <APYDS@discussions.microsoft.com> wrote in message
> news:9CCEFB16-A13F-4805-B28F-8CBD94F094D5@microsoft.com...
> > 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(B24>C24, 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?
>
>
>