Calculate Login hours

Hi All,
I am trying to calculate the Login hours.Below example shows login time and logout time of employees. An employee login hours should be canculated from his First login time to his Last logout time. ex 3200 employee login hours is 10 hours.

Id Login Time Login Date Logout Time Logout Date
3200 17:15 10/24/2010 17:46 10/24/2010
3200 17:50 10/24/2010 22:45 10/24/2010
3200 22:46 10/24/2010 3:15 10/25/2010
3201 20:00 10/24/2010 4:00 10/25/2010
3215 20:15 10/24/2010 4:00 10/25/2010
3218 0:45 10/24/2010 11:45 10/24/2010
3245 20:00 10/24/2010 4:00 10/25/2010
3266 20:00 10/24/2010 4:00 10/25/2010
3299 20:00 10/24/2010 4:00 10/25/2010
4100 17:00 10/24/2010 17:10 10/24/2010
4103 20:00 10/24/2010 4:00 10/25/2010
4199 8:00 10/24/2010 16:00 10/25/2010

Not sure but maybe

=SUM(MOD((E2:E13+D2:D13)-(C2:C13+B2:B13),1))

confirmed with Ctrl+Shift+Enter and formatted as [h]:mm. Result is 76:50. Is that what you want?

I think more like :-

=SUMPRODUCT(((E2:E13+D2:D13)-(C2:C13+B2:B13))*(A2:A13=J4))

Where J4 is the employee number, gives 9.55 for 3200 which is correct by my math

31 mins +4h 55m + 4h 29m = 9h55min

but if you meant the difference between the first and last time for an employee then :-

=SUMPRODUCT(MAX(($D$2:$D$13+$E$2:$E$13)*(A19=$A$2:$A$13))-MIN((1000000*($A$2:$A$13<>A19))+$C$2:$C$13+$B$2:$B$13))

would work

you have to format the cells to a custom time format [h]:mm, otherwise it wont show hours over 24!

See the sheet I attached, the formula I just sent would give the way you want!

You could also try this formula in B18 copied down

=LOOKUP(2,1/(A$2:A$13=A18),D$2:D$13+E$2:E$13)-SUM(INDEX(B$2:C$13,MATCH(A18,A$2:A$13,0),0))

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Administrative Note:

Hello gcabz.
Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread