Sumarize working hours per Employee per Day based on check in, check out time

[kuldokk]

Hi guys,

I have a small problem here like I wrote in the title. How can I find out how many hours an employee has already worked in a particular day (Column H).
Given the fact is an employee may check in and check out multiple times per day.

With my current solution I need so many steps that I would rather not go with it: Split check in and check out time to 2 columns, line up check in and check out to 1 row, perform time difference calculation and then run SUMIFS formula to fill in the column H. This solution cannot account for the case that the last check in and check out fall on 2 consequent days.

Thank you for any suggestion.

 

[AlanY]

this solution uses helper column E

Excel 2012
	A	B	C	D	E	F	G	H
1	Date	Time	ID	Check In/Out		Date	ID	Hrs
2	23/03/2016	13:15:25	A	Check In	14:15:25	23/03/2016	A	2
3	23/03/2016	14:15:25	A	Check Out	14:15:25	23/03/2016	B	1
4	23/03/2016	15:15:25	A	Check In	16:15:25
5	23/03/2016	16:15:25	A	Check Out	16:15:25
6	22/03/2016	17:15:25	A	Check In	18:15:25
7	22/03/2016	18:15:25	A	Check Out	18:15:25
8	23/03/2016	19:15:25	B	Check In	20:15:25
9	23/03/2016	20:15:25	B	Check Out	20:15:25
10	23/03/2016
11	23/03/2016
12	23/03/2016
13	23/03/2016
14	23/03/2016
Sheet3

Cell Formulas
Range	Formula
E2	=IF(D3="Check Out",B3,B2)
H2	=24*SUMPRODUCT(($A$2:$A$14=F2)*($C$2:$C$14=G2),$E$2:$E$14-$B$2:$B$14)