Feb 18 2020 09:49 PM
Good day,
I'm looking for a formula that can get total hours worked per person given that there are multiple INs, and OUTs because of break time.
attached is the daily time record for the month of January.
advance thanks.
Christian
ID | Employee | Time In 1 | Time Out 1 | Time In 2 | Time Out 2 | Time In 3 | Time Out 3 | Time In 4 | Time Out 4 | Time In 5 | Time Out 5 | Total hourswork |
1062 | SHERYL MARIANO | 11:22:11 | 13:55:02 | 14:54:44 | 19:11:00 | 19:26:32 | 19:26:32 | |||||
1203 | CINDY LAUZON | 13:15:10 | 15:57:46 | 16:57:32 | 20:01:11 | 20:16:23 | 20:16:23 | |||||
411 | ALYSSA EUGENIO | 10:44:19 | 14:30:01 | 15:25:48 | 20:00:02 | 20:15:33 | ||||||
1153 | FLORDELYN G | 12:14:44 | 16:31:08 | 17:30:41 | 20:32:48 | 20:47:10 | ||||||
1141 | MARY CRIS E | 13:13:47 | 15:51:03 | 16:51:42 | 21:15:01 | 21:30:00 | ||||||
949 | MARICAR ROSALES | 11:58:52 | 13:39:03 | 14:39:15 | 18:37:01 | 18:51:24 | ||||||
602 | MARGILYN B. | 11:45:59 | 13:11:16 | 14:07:39 | 19:08:25 | 19:18:29 | ||||||
1089 | JORELYN R | 12:14:24 | 14:23:10 | 15:23:26 | 19:33:04 | 19:47:58 | ||||||
1092 | MYRANAR CERENO | 11:50:47 | 14:10:01 | 15:10:23 | 19:54:02 | 20:08:37 | 21:15:23 | |||||
997 | JENNY BARRAQUIO | 17:19:18 | 20:34:45 | 21:15:36 | ||||||||
1176 | JHOANN PINCA | 11:53:10 | 15:10:37 | 16:11:06 | 19:11:10 | 19:26:54 | ||||||
120 | JUDY | 11:04:15 | 14:07:36 | 15:05:28 | 18:50:11 | 19:01:15 |
Feb 19 2020 12:29 AM
@ChristianMercado If you can guarantee that your time records are clean, it could be as simple as entering the formula below on O2 and copy it down.
=F2+H2+J2+L2+N2-E2-G2-I2-K2-M2
However, the date provided includes many records (almost in 60% of them) with "Time IN" but no Time OUT". The opposite occurs as well, though less frequent. How would you handle those cases? None of the time records seem to carry over to the next day (i.e. start before midnight with an end after midnight). Can you confirm that this will indeed never happen?
Feb 19 2020 12:55 AM - edited Feb 19 2020 12:56 AM
This array formula might be helpful, but only criteria is you must have both In & out date, any one of them is missing then it will consider it as zero. as rightly said by @Riny_van_Eekelen your time records are must be clean.
- Copy the below formula
- Paste in the cell & press ctrl + shift + enter
=SUM(IFERROR(TIMEVALUE(IF(SEARCH("Out",$F$1:$N$1),F2:N2))-TIMEVALUE(IF(SEARCH("In",$E$1:$M$1),E2:M2)),0))
Please see the attached file with the formula
Regards, Faraz Shaikh, MCT, MIE, MOS Master, Excel Expert
Feb 19 2020 01:08 AM
Feb 19 2020 01:50 AM
As variant
=SUMPRODUCT(($F2:$N2-$E2:$M2)*($F2:$N2>0)*($E2:$M2>0)*ISEVEN(COLUMN(F2:N2)))