how do I count a rota with no dates and using 24 hours clock

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?

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?
>
>
>