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

[Guest]

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?

 

[Art Farrell]

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

 

[Guest]

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