Oct 08 2019 02:54 AM
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 | tout1 | tout2 | tout3 | tin4 | tout4 | hourswork |
9/1/2019 | Sunday | MARY JOY ALJAS | 12:14 PM | 4:58 PM | 7:49 PM | 4:00 PM | 7:32 PM | 9:15 PM | 7.72 | ||
9/1/2019 | Sunday | SARAH GERONIMO | 12:14 PM | 3:42 PM | 6:41 PM | 2:45 PM | 6:28 PM | 9:15 PM | 7.8 | ||
9/1/2019 | Sunday | LORIEBERN B. | 9:23 AM | 2:40 PM | 6:24 PM | 1:37 PM | 6:07 PM | 6:48 PM | 8.07 | ||
9/1/2019 | Sunday | ROSSANN LADESMA | 9:22 AM | 1:17 PM | 6:40 PM | 12:18 PM | 6:24 PM | 6:48 PM | 8.13 | ||
9/2/2019 | Monday | LORIEBERN B. | 12:12 PM | 4:18 PM | 8:02 PM | 3:14 PM | 7:46 PM | 9:15 PM | 7.67 | ||
9/2/2019 | Monday | SARAH GERONIMO | 9:42 AM | 1:47 PM | 6:27 PM | 12:48 PM | 6:12 PM | 6:50 PM | 7.87 | ||
9/2/2019 | Monday | MARY JOY ALJAS | 9:42 AM | 2:56 PM | 7:24 PM | 1:56 PM | 7:09 PM | 9:15 PM | 10.27 |
DESIRED OUTPUT
Date | TIME | IN | OUT | # OF STAFF |
9/1/2019 | 8:00:00 AM | 0 | 0 | 0 |
9/1/2019 | 9:00:00 AM | 2 | 0 | 2 |
9/1/2019 | 10:00:00 AM | 0 | 0 | 2 |
9/1/2019 | 11:00:00 AM | 0 | 0 | 2 |
9/1/2019 | 12:00:00 PM | 1 | 1 | 2 |
9/1/2019 | 1:00:00 PM | 1 | 1 | 2 |
9/1/2019 | 2:00:00 PM | 1 | 1 | 2 |
9/1/2019 | 3:00:00 PM | 1 | 0 | 3 |
9/1/2019 | 4:00:00 PM | 0 | 0 | 3 |
9/1/2019 | 5:00:00 PM | 0 | 0 | 3 |
9/1/2019 | 6:00:00 PM | 3 | 5 | 1 |
9/1/2019 | 7:00:00 PM | 0 | 0 | 1 |
9/1/2019 | 8:00:00 PM | 0 | 0 | 1 |
9/1/2019 | 9:00:00 PM | 0 | 1 | 0 |
9/1/2019 | 10:00:00 PM | 0 | 0 | 0 |
9/2/2019 | 8:00:00 AM | 0 | 0 | 0 |
9/2/2019 | 9:00:00 AM | 1 | 0 | 1 |
9/2/2019 | 10:00:00 AM | 0 | 0 | 1 |
9/2/2019 | 11:00:00 AM | 0 | 0 | 1 |
9/2/2019 | 12:00:00 PM | 1 | 1 | 1 |
9/2/2019 | 1:00:00 PM | 1 | 0 | 2 |
9/2/2019 | 2:00:00 PM | 0 | 0 | 2 |
9/2/2019 | 3:00:00 PM | 0 | 1 | 1 |
9/2/2019 | 4:00:00 PM | 1 | 0 | 2 |
9/2/2019 | 5:00:00 PM | 0 | 0 | 2 |
9/2/2019 | 6:00:00 PM | 1 | 2 | 1 |
9/2/2019 | 7:00:00 PM | 0 | 1 | 0 |
9/2/2019 | 8:00:00 PM | 1 | 0 | 1 |
9/2/2019 | 9:00:00 PM | 0 | 1 | 0 |
9/2/2019 | 10:00:00 PM | 0 | 0 | 0 |
please see attached file
thank you
Christian
Oct 08 2019 07:04 AM
SolutionI'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))
Oct 08 2019 05:49 PM
@Sergei Baklan really great help. thank you very much for this.
Oct 14 2019 07:17 PM
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.
Oct 15 2019 03:04 AM
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.
Oct 15 2019 05:38 PM
Oct 16 2019 01:10 PM
Christian, glad to help
Oct 08 2019 07:04 AM
SolutionI'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))