SOLVED

How to Count No. of Staff per hour

Copper Contributor

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

datedatenametin1tin2tin3tout1tout2tout3tin4tout4hourswork
9/1/2019SundayMARY JOY ALJAS12:14 PM4:58 PM7:49 PM4:00 PM7:32 PM9:15 PM  7.72
9/1/2019SundaySARAH GERONIMO12:14 PM3:42 PM6:41 PM2:45 PM6:28 PM9:15 PM  7.8
9/1/2019SundayLORIEBERN B.9:23 AM2:40 PM6:24 PM1:37 PM6:07 PM6:48 PM  8.07
9/1/2019SundayROSSANN LADESMA9:22 AM1:17 PM6:40 PM12:18 PM6:24 PM6:48 PM  8.13
9/2/2019MondayLORIEBERN B.12:12 PM4:18 PM8:02 PM3:14 PM7:46 PM9:15 PM  7.67
9/2/2019MondaySARAH GERONIMO9:42 AM1:47 PM6:27 PM12:48 PM6:12 PM6:50 PM  7.87
9/2/2019MondayMARY JOY ALJAS9:42 AM2:56 PM7:24 PM1:56 PM7:09 PM9:15 PM  10.27

 

DESIRED OUTPUT

DateTIMEINOUT# OF STAFF
9/1/20198:00:00 AM000
9/1/20199:00:00 AM202
9/1/201910:00:00 AM002
9/1/201911:00:00 AM002
9/1/201912:00:00 PM112
9/1/20191:00:00 PM112
9/1/20192:00:00 PM112
9/1/20193:00:00 PM103
9/1/20194:00:00 PM003
9/1/20195:00:00 PM003
9/1/20196:00:00 PM351
9/1/20197:00:00 PM001
9/1/20198:00:00 PM001
9/1/20199:00:00 PM010
9/1/201910:00:00 PM000
9/2/20198:00:00 AM000
9/2/20199:00:00 AM101
9/2/201910:00:00 AM001
9/2/201911:00:00 AM001
9/2/201912:00:00 PM111
9/2/20191:00:00 PM102
9/2/20192:00:00 PM002
9/2/20193:00:00 PM011
9/2/20194:00:00 PM102
9/2/20195:00:00 PM002
9/2/20196:00:00 PM121
9/2/20197:00:00 PM010
9/2/20198:00:00 PM101
9/2/20199:00:00 PM010
9/2/201910:00:00 PM000

 

please see attached file

 

thank you

Christian

 

@Twifoo 

7 Replies
best response confirmed by ChristianMercado (Copper Contributor)
Solution

@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))

@Sergei Baklan really great help. thank you very much for this.

@ChristianMercado , you are welcome

hi @Sergei Baklan,

 

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.

 

 

@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.

@Sergei Baklan  

Great help sir.

 

thank you very much.

Christian

@ChristianMercado 

Christian, glad to help

1 best response

Accepted Solutions
best response confirmed by ChristianMercado (Copper Contributor)
Solution

@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))

View solution in original post