counting how many employees are scheduled to work by hour


A

amin

Hi,

I am using the below formula to count how many employees are scheduled to
work by hour but it is not counting if the shift starts in the PM and ends in
the AM.

=SUMPRODUCT(($B$3:$B$24<=$A36)*($C$3:$C$24>=$A37))

Thank you for your help in advance.
 
Ad

Advertisements

S

Sean Timmons

=IF($A36>$A37,SUMPRODUCT(($B$3:$B$24<=$A36)*($C$3:$C$24>=$A37)),SUMPRODUCT(($B$3:$B$24>=$A36)+($C$3:$C$24<=$A37)))
 
A

amin

Thanks Sean for the response.

It is not giving me the desired results. I only have 2 members schedled on
Saturday. One from 10 AM to 9 PM and the other from 10 PM to 8 AM.

A36 is 12:00 AM so the result should be 1.

The formula you provided is giving me 42.

Any ideas?
 
Ad

Advertisements

B

barry houdini

Try like this

=SUMPRODUCT((($B$3:$B$24<=$A36)+($C$3:$C$24>=$A37)+($B$3:$B$24>$C$3:$C
$24)=2)+0)

regards, daddy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top