I want to enter a count formula for a rota I'm doing for my staff using the
24 hour clock. Basically I want to be able to count the number of staff
working in the rota between two selected times without having to put dates
into the rota. The staff work 24 hour shifts so the count has to take
account of the fact that if the rota says a staff member worked between, say,
20:00 and 8:00 that member of staff worked between 8:00 pm on one day to
8:00 am on the following day.

For Monday, for instance, cells b6:b21 contain the start times for the
respective members of staff and cells c6:c21 the end times. Cell B24
contains the selected start time and C24 the selected end time for the
purposes of the count.

I have tried various formulas including:

=IF(B24=C24, (COUNT(AND(b6:b21=b24, c6:c21=(c24+1))),(AND(B6:B21=B24,
C6:C21<=C24)))

=COUNT(IF(AND((B24C24)*(B6:B21=B24)*(C6:C21<=(C2 4+1))),
(AND(B6:B21=B24)*(C6:C21<=C24))))

=COUNT(IF(AND((B24C24),(B6:B21=B24),(C6:C21<=(C2 4+1))),
(AND(B6:B21=B24)*(C6:C21<=C24))))

If the above sounds too complicated, all I want to do is:

if b24 is higher than c24 count where b6:b21 is higher than b24 and c6:c21
is lower than c24 plus 1. If it is not higher than do the same thing except
don't add one at the end.

Thanks in advance for any help out there.

Hi APYDS,

Try the following:

for individual cells calculation:
=B1-A1+(A1B1)

for range calculation:
=SUMPRODUCT(B1:B20-A1:A20+(A1:A20B1:B20))

don't forget to format the cell with the result as time.

Regards,
KL


"APYDS" wrote in message
...
I want to enter a count formula for a rota I'm doing for my staff using the
24 hour clock. Basically I want to be able to count the number of staff
working in the rota between two selected times without having to put dates
into the rota. The staff work 24 hour shifts so the count has to take
account of the fact that if the rota says a staff member worked between,
say,
20:00 and 8:00 that member of staff worked between 8:00 pm on one day to
8:00 am on the following day.

For Monday, for instance, cells b6:b21 contain the start times for the
respective members of staff and cells c6:c21 the end times. Cell B24
contains the selected start time and C24 the selected end time for the
purposes of the count.

I have tried various formulas including:

=IF(B24=C24, (COUNT(AND(b6:b21=b24, c6:c21=(c24+1))),(AND(B6:B21=B24,
C6:C21<=C24)))

=COUNT(IF(AND((B24C24)*(B6:B21=B24)*(C6:C21<=(C2 4+1))),
(AND(B6:B21=B24)*(C6:C21<=C24))))

=COUNT(IF(AND((B24C24),(B6:B21=B24),(C6:C21<=(C2 4+1))),
(AND(B6:B21=B24)*(C6:C21<=C24))))

If the above sounds too complicated, all I want to do is:

if b24 is higher than c24 count where b6:b21 is higher than b24 and c6:c21
is lower than c24 plus 1. If it is not higher than do the same thing
except
don't add one at the end.

Thanks in advance for any help out there.

Thanks for trying KL but I don't actually want to count the amount of time
each person has worked between two selected times; I want to count how many
people are working between two selected times (taking into account a night
shift). If I have misunderstood your formula, sorry in advance but I don't
think it does what I'm looking for. Can KL or anyone else please help.

"KL" wrote:

Hi APYDS,

Try the following:

for individual cells calculation:
=B1-A1+(A1B1)

for range calculation:
=SUMPRODUCT(B1:B20-A1:A20+(A1:A20B1:B20))

don't forget to format the cell with the result as time.

Regards,
KL


"APYDS" wrote in message
...
I want to enter a count formula for a rota I'm doing for my staff using the
24 hour clock. Basically I want to be able to count the number of staff
working in the rota between two selected times without having to put dates
into the rota. The staff work 24 hour shifts so the count has to take
account of the fact that if the rota says a staff member worked between,
say,
20:00 and 8:00 that member of staff worked between 8:00 pm on one day to
8:00 am on the following day.

For Monday, for instance, cells b6:b21 contain the start times for the
respective members of staff and cells c6:c21 the end times. Cell B24
contains the selected start time and C24 the selected end time for the
purposes of the count.

I have tried various formulas including:

=IF(B24=C24, (COUNT(AND(b6:b21=b24, c6:c21=(c24+1))),(AND(B6:B21=B24,
C6:C21<=C24)))

=COUNT(IF(AND((B24C24)*(B6:B21=B24)*(C6:C21<=(C2 4+1))),
(AND(B6:B21=B24)*(C6:C21<=C24))))

=COUNT(IF(AND((B24C24),(B6:B21=B24),(C6:C21<=(C2 4+1))),
(AND(B6:B21=B24)*(C6:C21<=C24))))

If the above sounds too complicated, all I want to do is:

if b24 is higher than c24 count where b6:b21 is higher than b24 and c6:c21
is lower than c24 plus 1. If it is not higher than do the same thing
except
don't add one at the end.

Thanks in advance for any help out there.