Forum Discussion

ChristianMercado's avatar
ChristianMercado
Copper Contributor
Oct 08, 2019
Solved

How to Count No. of Staff per hour

please help me!   I just want to count the total number of staff per hour per day given the raw data below. please see the desired output below.   RAW DATA date date name tin1 tin2 tin3...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 08, 2019

    ChristianMercado 

    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))
ChristianMercado's avatar
ChristianMercado
Copper Contributor
Oct 14, 2019

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.

 

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 15, 2019

ChristianMercado 

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.