Calculating Total hours worked

[ChristianMercado]

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

[#2]

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

[#3]

Hi @ChristianMercado, 

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 

www.ExcelExciting.com

[#4]

yeah, the record isn't clean because they are not taking it seriously.

Thank you very much anyway.

Christian

[#5]

@ChristianMercado 

As variant

=SUMPRODUCT(($F2:$N2-$E2:$M2)*($F2:$N2>0)*($E2:$M2>0)*ISEVEN(COLUMN(F2:N2)))

[#6]

Thanks for helping me