Forum Discussion
How to Count No. of Staff per hour
I'm not sure I understood your logic correctly since results are bit different, formulas are
IN =SUMPRODUCT(($E$2:$G$106>$Q2)*($E$2:$G$106<($Q2+1/24))*($A$2:$A$106=$P2)) OUT =SUMPRODUCT(($H$2:$J$106>$Q2)*($H$2:$J$106<($Q2+1/24))*($A$2:$A$106=$P2)) # OF STAFF =SUMPRODUCT(($H$2:$J$106>$Q2)*($E$2:$G$106<($Q2+1/24))*($A$2:$A$106=$P2))
I'm not sure I understood your logic correctly since results are bit different, formulas are
IN
=SUMPRODUCT(($E$2:$G$106>$Q2)*($E$2:$G$106<($Q2+1/24))*($A$2:$A$106=$P2))
OUT
=SUMPRODUCT(($H$2:$J$106>$Q2)*($H$2:$J$106<($Q2+1/24))*($A$2:$A$106=$P2))
# OF STAFF
=SUMPRODUCT(($H$2:$J$106>$Q2)*($E$2:$G$106<($Q2+1/24))*($A$2:$A$106=$P2))
SergeiBaklan really great help. thank you very much for this.
ChristianMercado , you are welcome
hi SergeiBaklan,
I need your help.
I want to compute for the total hours worked per employee. I used basic formula Time out - Time in but I get negative results when there is a blank cell.
Thank you in advance.
Blank cells are considered as zero, or 00:00 as the time. How to handle the situation when you have, for example, 3 time-ins and only 2 time-outs (which means one of time-outs wasn't put into the system) depends on your business logic.
If simply ignore pairs for which time-in or time-out is blank, the formula could be
=SUMPRODUCT((J2:L2-G2:I2)*(G2:I2>0)*(J2:L2>0)*24)Please check in column P attached.