• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help of Time IN & Time Out Calculation

Y

Yogesh

New Member
Hi All,

I need help to calculate the actual work hours from the difference of Time IN & Time Out (Door Access) data.

Also want to calculate time taken out during first & last swipe.

Emp No Name Card No Swipe Date SwipeTime Swipe Status
1234 ABC 5678 2016-04-02 03:41:13 Time IN
1234 ABC 5678 2016-04-02 03:47:36 Time IN
1234 ABC 5678 2016-04-02 05:03:57 Time OUT
1234 ABC 5678 2016-04-02 05:04:21 Time OUT
1234 ABC 5678 2016-04-02 12:43:10 Time IN
1234 ABC 5678 2016-04-02 16:02:55 Time OUT
 
Hi Yogesh, and welcome to the forum! :awesome:

I'm not quite sure how someone could check in twice in a row and then out twice in a row, but assuming actual data is all pairs of in/out, you could do something like:

=SUMPRODUCT((D2:D7+E2:E7)*(F2:F7="Time OUT"))-SUMPRODUCT((D2:D7+E2:E7)*(F2:F7="Time IN"))

Delta between first and last swipe would be similar, but you can just do a regular subtraction. (since it's only two rows involved)
 
You must log in or register to reply here.
Back
Top